Forum Discussion

SamTailor's avatar
SamTailor
Helper I
10 years ago
Solved

Monthly Average Grand Total calculation - Help needed

Hi!   I'm new in Power BI and can't figure out how to do a tricky thing with Grand Total of Average I have a table with Monthly averages, but grand totalin Power BI shows me SUM of these averages....
Need Help
Tips and Tricks
  • AlexChen's avatar
    AlexChen
    10 years ago

    Hi,

     

    In this scenario, if you include “Direction” column into your table visual, definitely your measure will be sliced by “direction”. This is how DAX works. So you need to group on month in your calculation.

     

    AverageMeasure = CALCULATE(AVERAGEA(Direction[AV Plan]),ALLEXCEPT(Direction,Direction[Month #]))

     

     

    As to show Year Average basing on number of days in your top table of this case(I assume it’s called “Direction2”), You can create a following measure:

     

    avgMeasure = CALCULATE(sumx(Direction2, Direction2[monthDay] * Direction2[Average]) / sumx(Direction2, Direction2[monthDay]))

     

     

    Best Regards

    Alex

     

     

     

AlexChen's avatar
AlexChen
Microsoft Employee
10 years ago

Hi,

 

In this scenario, if you include “Direction” column into your table visual, definitely your measure will be sliced by “direction”. This is how DAX works. So you need to group on month in your calculation.

 

AverageMeasure = CALCULATE(AVERAGEA(Direction[AV Plan]),ALLEXCEPT(Direction,Direction[Month #]))

 

 

As to show Year Average basing on number of days in your top table of this case(I assume it’s called “Direction2”), You can create a following measure:

 

avgMeasure = CALCULATE(sumx(Direction2, Direction2[monthDay] * Direction2[Average]) / sumx(Direction2, Direction2[monthDay]))

 

 

Best Regards

Alex

 

 

 

  • SamTailor's avatar
    SamTailor
    Helper I
    10 years ago

    Hi ALex,

     

    Thanks for your reply. I still can't make it work the way U do. I created the measure for Direction2 and this is what i got

     

     

     

     

    The problem is that Number of days is 186 in my case - Power BI sums it((( How have you created a column "monthDay"? - i believe its the key queestion to solve the problem....

    • AlexChen's avatar
      AlexChen
      Microsoft Employee
      10 years ago

      Hi,

       

      You can go to powerbi desktop.  choose "monthDay" column.

       

       

      choose "Default Summarization: Do Not Summarize".

       

       

      do the samething for column "month".

       

      Now remove your table visual on "Direction2" then create a new one.

       

      Best Regards

      Alex

       

       

      • SamTailor's avatar
        SamTailor
        Helper I
        10 years ago

        When I do so - it simply removes Grand Totals, the calculation logic is still the same - formula takes SUM of days in each month and it gives me wrong result 11 153. How have you made the formula to calculculate not the sum of days in table but simply nubber of days?