cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sagejah9
Helper III
Helper III

Grand total Average is not accurate

Hi, It doesn't seem like measure is showing the correct average at the grand total row. I have a table with 3 rows 
the measure calculates each row in the table correctly however it doesn't calculate the average of the 3 rows. It calculates the average of unsummarized level. However the table visual is showing a summarized level and I would like it to calculate the average correctly regardless which level. 

Order NumberVarAverage of Var
o38921100%
o37921100%
o273900%
totalnot summarized90.91%, should show 66.67%

 

it is showing 90.91% because its calculating the average on 11 rows at the granular level. But the table is a roll up view.

This was my measure
Average of Var = CALCULATE(AVERAGE(Table1 [Var] ))

1 ACCEPTED SOLUTION

Hi,

I don't think what you looking to do is possible "directly".

 

It's a fundamentally different calculation as one is calculating an average for each order and then averaging them together. The other is a straight average.

 

You can deal with lots of stuff at different levels of hierachy using ISINSCOPE/ISFILTERED type tests however none of that helps you when you get to the total row because on the total row none of the columns you're interested in exist in the FILTER context.

 

The only way round I can think of doing it is having a disconnected table with two values in a grain column: "Order Grain" and "Item Grain"

You can then drop that into the visual level filter and use the following measure:

Avg at Correct Grain = 

VAR OrderGrain = 
    AVERAGEX(
        VALUES ( Table1[OrderNum] ),
        CALCULATE ( AVERAGE ( Table1[Var] ) )
    )

VAR ItemGrain = 
    AVERAGE ( Table1[Var] )

VAR SelectedGrain = SELECTEDVALUE ( GrainSelector[Grain] )

RETURN 
    IF ( 
        SelectedGrain = "Order Grain", 
        OrderGrain, 
        ItemGrain 
    )

 

bcdobbs_0-1643820009831.pngbcdobbs_1-1643820035199.png

(Note my demo data is different from yours)


Personally I'd normally just have two different measures for each grain.

 

If someone has a more elegant solution I'd be interested but based on my understanding of filter contexts I don't think there is one.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

6 REPLIES 6
bcdobbs
Super User
Super User

Are you able to supply a demo pbix file?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

You can use an IF statement use HASONEFILTER to identify what grain you're working at:

 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

OrderNum varAverage Var %
O938341100%
O3782400%
O3792400%
O528281100%
Total 57%

should show 50% at grand total

 

 

OrderNumItem#VarAverage Var %
O9383413/32421100%
O9383413/37841100%
O9383411/13741100%
O5282833/38201100%
O3792434/472900%
O3792412/372900%
O3782411/574800%
Total  57%

 

@bcdobbs , I hope this helps to explain. I want the Average Var% measure to work regardless of ordernumber or itemnumber. what is shown in the visua should be calculated

 

Hi,

I don't think what you looking to do is possible "directly".

 

It's a fundamentally different calculation as one is calculating an average for each order and then averaging them together. The other is a straight average.

 

You can deal with lots of stuff at different levels of hierachy using ISINSCOPE/ISFILTERED type tests however none of that helps you when you get to the total row because on the total row none of the columns you're interested in exist in the FILTER context.

 

The only way round I can think of doing it is having a disconnected table with two values in a grain column: "Order Grain" and "Item Grain"

You can then drop that into the visual level filter and use the following measure:

Avg at Correct Grain = 

VAR OrderGrain = 
    AVERAGEX(
        VALUES ( Table1[OrderNum] ),
        CALCULATE ( AVERAGE ( Table1[Var] ) )
    )

VAR ItemGrain = 
    AVERAGE ( Table1[Var] )

VAR SelectedGrain = SELECTEDVALUE ( GrainSelector[Grain] )

RETURN 
    IF ( 
        SelectedGrain = "Order Grain", 
        OrderGrain, 
        ItemGrain 
    )

 

bcdobbs_0-1643820009831.pngbcdobbs_1-1643820035199.png

(Note my demo data is different from yours)


Personally I'd normally just have two different measures for each grain.

 

If someone has a more elegant solution I'd be interested but based on my understanding of filter contexts I don't think there is one.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
bcdobbs
Super User
Super User

You need to do something like:

Average of Var = 
AVERAGEX(
VALUES ( Table1[Order Number] ),
CALCULATE ( AVERAGE ( Table1[Var] ) )
)


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

@bcdobbs Thanks, This works but I would like the measure to work regardless of the column. So at the granular level if I choose to see the Average of var by Items  it should work it out correctly and if I choose to see by OrderNumber which is parent in the Hierachy it should calculate correctly as well. 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors