Forum Discussion
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.
What i want him to shows me in Grand Total is Year Average basing on number of days.
This is how i do it in Excel
Plese Help, maybe there is some formula to solve this ?
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
11 Replies
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
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....
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
Couple things you could do. In your table settings, click the drop down arrow next to "Average Plan" and choose "Average" instead of "Sum".
You could also create a Measure like:
MyAverage = AVERAGE(Averages[Average Plan])
Use that in your table instead.
I'm afraid this method gives a little different result - 66 885 instead of 66 920
Correct, this is averaging all of the averages, which will return a different result than taking the SUM total and dividing by the total number of days. Lots of rounding issues going on. I can't figure out why your numbers are different than mine in Excel (see below).
So, basically, I'm not sure of your source data, but if you have something like this:
MonthDays Average 31 70425 29 60138 31 59234 30 58987 31 58371 30 61831 31 63388 31 63795 30 69316 31 74747 30 78325 31 84066 Then create a calculated column:
MonthAverageNumber = [MonthDays] * [Average]
Then create a measure like:
MyMeasure = SUM([MonthAverageNumber]) / SUM(MonthDays)
