PowerBi: Calculating the Average Daily Canceled Orders

Question

I am somewhat new to PowerBi and been doing some investigating. For this example, please refer to the table. The table is called Table1.

[Table1]
month_desc  month-day   Order Status
Jul         7-Jul       Canceled
Jul         8-Jul       Complete
Jul         2-Jul       Complete
Jul         14-Jul      Complete
Jul         21-Jul      Complete
Jul         7-Jul       Canceled
Jul         8-Jul       Complete
Jul         14-Jul      Complete
Jul         14-Jul      Complete
Jul         22-Jul      Canceled
Jul         8-Jul       Complete
Jul         16-Jul      Complete
Jul         9-Jul       Canceled
Aug         11-Aug      Complete
Aug         18-Aug      Complete
Jul         14-Jul      Canceled

The data above is partial data to give an idea of the data. Also, the data will not included Saturdays and Sundays The equation to get the average daily canceled is the number of canceled by month / the number of days in the month.

I can get the number of canceled orders in DAX

Count of Canceled Order = CALCULATE(COUNTROWS('Table1'),filter('Table1','Table1'[Order Status]="CANCELED"))

How do I get the number of days per month in Table1 and use it to divide 'Count of Canceled Order' to get a new measure for the average daily canceled orders? This measure would have to be dynamic.

Or can I get this to work by using AVERAGEX function?

mkRabbani mkRabbani · Accepted Answer · 2020-09-10T05:17:09

Here is your measure for counting Canceled orders-

Count of Canceled Order = 

CALCULATE(
    COUNTROWS('Table1'),
    filter(
        'Table1',
        'Table1'[Order Status]="CANCELED"
    )
) + 0

Now, Create a new measure as below-

Count of days in Month = DISTINCTCOUNT(Table1[month-day])

And finally create this below measure for the average calculation-

average = [Count of Canceled Order]/[Count of days in Month]

Note: you can also incorporate all measures in one to get the final average as below if you required-

average2 = 

VAR Count_of_Canceled_Order = 
CALCULATE(
    COUNTROWS('Table1'),
    filter(
        'Table1',
        'Table1'[Order Status]="CANCELED"
    )
) + 0

VAR Count_of_days_in_Month = DISTINCTCOUNT(Table1[month-day])

RETURN Count_of_Canceled_Order/Count_of_days_in_Month

The results of different measure will be as below-

PowerBi: Calculating the Average Daily Canceled Orders

1 Answers