Forum Discussion

SamTailor's avatar
SamTailor
Icon for Helper I rankHelper 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
Icon for Microsoft Employee rankMicrosoft 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
Icon for Helper I rankHelper 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!