Forum Discussion
Helper IMonthly Average Grand Total calculation - Help needed
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
Community ChampionCouple 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.
SamTailorI'm afraid this method gives a little different result - 66 885 instead of 66 920
- Greg_Deckler
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)
- SamTailor
Unfortunatelly this also gives me different result
I guess thats becouse i have several rows for each month in my data source. Here is it:
Direction EOM Date AV Plan Month # NumberDays 1 01.31.16 -72,54979045 1 31 1 02.29.16 -252,5844382 2 29 1 03.31.16 -63,70552772 3 31 1 04.30.16 -1243,54721 4 30 1 05.31.16 -1693,615029 5 31 1 06.30.16 -684,3135524 6 30 1 07.31.16 -815,8175674 7 31 1 08.31.16 -582,0372591 8 31 1 09.30.16 -0,855819632 9 30 1 10.31.16 -565,843631 10 31 1 11.30.16 -1094,520502 11 30 1 12.31.16 -1736,279441 12 31 2 01.31.16 17319,95 1 31 5 01.31.16 16387,36479 1 31 3 01.31.16 9066,485 1 31 4 01.31.16 9398,75 1 31 6 01.31.16 18324,5 1 31 2 02.29.16 14860 2 29 5 02.29.16 14084,67444 2 29 3 02.29.16 7187,5 2 29 4 02.29.16 8158,5 2 29 6 02.29.16 16100 2 29 2 03.31.16 15130 3 31 5 03.31.16 13922,29553 3 31 3 03.31.16 7310 3 31 4 03.31.16 7985 3 31 6 03.31.16 14950 3 31 2 04.30.16 16060 4 30 5 04.30.16 14338,91866 4 30 3 04.30.16 7804,128545 4 30 4 04.30.16 8077 4 30 6 04.30.16 13950 4 30 2 05.31.16 16550 5 31 5 05.31.16 14218,31442 5 31 3 05.31.16 7865,225611 5 31 4 05.31.16 8231,5 5 31 6 05.31.16 13200 5 31 2 06.30.16 17790 6 30 5 06.30.16 14962,64149 6 30 3 06.30.16 8421,097065 6 30 4 06.30.16 8416,5 6 30 6 06.30.16 12925 6 30 2 07.31.16 18160 7 31 5 07.31.16 15206,49257 7 31 3 07.31.16 8813 7 31 4 07.31.16 8549,5 7 31 6 07.31.16 13475 7 31 2 08.31.16 17090 8 31 5 08.31.16 15227,61101 8 31 3 08.31.16 9023 8 31 4 08.31.16 8836,5 8 31 6 08.31.16 14200 8 31 2 09.30.16 17959 9 30 5 09.30.16 16834,61978 9 30 3 09.30.16 9957,134789 9 30 4 09.30.16 9516 9 30 6 09.30.16 15050 9 30 2 10.31.16 19959 10 31 5 10.31.16 18445,25884 10 31 3 10.31.16 10807,13479 10 31 4 10.31.16 10101 10 31 6 10.31.16 16000 10 31 2 11.30.16 21505 11 30 5 11.30.16 19142,378 11 30 3 11.30.16 11358,5 11 30 4 11.30.16 10513,5 11 30 6 11.30.16 16900 11 30 2 12.31.16 23755 12 31 5 12.31.16 20351,58694 12 31 3 12.31.16 12161 12 31 4 12.31.16 11385 12 31 6 12.31.16 18150 12 31 I understand that it doesn't work becouse of derections column, but I cant figure out how to explain Power BI to sum all rows inside directions, instead of calculatiing average of total rows number....