Monthly Average Grand Total calculation - Help needed

[SamTailor]

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 ?

[AlexChen · Accepted solution]

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]

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....

[AlexChen]

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

[SamTailor]

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]

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]

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!

[Greg_Deckler]

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.

[SamTailor]

I'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....

[Greg_Deckler]

Sorry, forgot to post my Excel. Not sure why my Excel is different than yours but I'll try again with your data.