Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Monthly Average

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 . 

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

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

     

     
  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    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/