Monthly Average

[Anonymous]

I have data in the following format. I'm quite new to PowerBi. I'm trying to find out monthly average for each of the categories below.

Can anyone please help me with DAX? I have to find out monthly average for column [Rate] considering both the categories [Location] and [Type]. I'm not sure how to solve this if I add two slicers with Location and category. 

Please see the following picture. Forming a similar graph is my target. I added two slicers with location and type to get this. But it plots sum of rate for each month. I want to plot average of rate for each month instead of sum . 

[tackytechtom]

Hi @Anonymous ,

I'd try it with the following measure:

MonthlyAverageRate = 
VAR _currMonth = MONTH ( SELECTEDVALUE ( Table[Date] ) )
RETURN 
CALCULATE (
    AVERAGE ( Table[Rate] ),
    REMOVEFILTERS ( Table[Date] ),
    MONTH ( Table[Date] ) = _currMonth
)

Here the result with the slicer on Location = 'D'

I created an additional measure called AverageRate which does not consider the Average per month requirement:

AverageRate = CALCULATE ( AVERAGE ( Table[Rate] ) ) 

Here you can see, on the most granular level AverageRate just shows the same as Rate, whereas MonthlyAverageRate does return the mean rate in that month (January 2021)

 Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

I have create a table "Create Average" and made this measure ==> 

AVERAGE = calculate(averagex('Create Average',[TotalRate]))