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

     

     

     

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?

 

 

AlexChen's avatar
AlexChen
Microsoft Employee
10 years ago

Hi Sam,

 

If you have many duplicated records in your Direction2 like below.

 

You can create another table to remove them.

 

Direction3 = Distinct(Direction2)

 

then create the table visual based on Direction3.

 

Best Regards

Alex

 

  • SamTailor's avatar
    SamTailor
    Helper I
    10 years ago

    Thanks!

     

    I created a caclulated table removing all duplicates, created a connection between this table and data and adoped your formula for it. It worked.

    Colunn = CALCULATE(sumx('Calc Table Plan';'Calc Table Plan'[NumberDays]) * sum (Plan[AV Plan]))

     

     

    Measure = SUM(Plan[Colunn]) / SUMX('Calc Table Plan';'Calc Table Plan'[NumberDays])

     

     

     

    I got the result I wanted, now I have rigth grand total calculatiing  and even if I slice it by Directions!