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
Helper IUnfortunatelly 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....
Community ChampionSorry, forgot to post my Excel. Not sure why my Excel is different than yours but I'll try again with your data.