Power BI Table Average Total Incorrect

[Anonymous]

Hi,

I selected a table from the visualization options. After adding my columns as an average rather than sum, I am receiving the wrong total. If that is just the way the calculation works for average, in this process how can I still use the average option and still get the total sum of what is showing on the table as an average. Please advise.

               

[anilgk]

What do we do if there are nested categories ?

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[HotChilli]

If you want to learn how to fix this :

it's in the 'The totals in your table/matrix are wrong' section here :

https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882 

If you just want to fix the specific issue , link a sample pbix and someone will get you the right DAX code.

[Anonymous]

@HotChilli 

Hi, I look at the link you have sent. I tried a couple examples hoping it might help. The examples I have tried from the link were not successful. 

This is what I have tried:

MinScoreMeasure = MINX ( SUMMARIZE ( ‘Table’, ‘Table’[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( ‘Table’, ‘Table’[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( ‘Table’, ‘Table’[Group] , "Measure",[YourMeasure] ), [Measure])

My goal, is to use average as shown below from the selection. As for the total average, shown in the second image, I want to get the accumulated total from the average numbers on the table.

[v-kelly-msft]

Hi @Anonymous ,

You should use SUMX instead of "MINX"," MAXX" and "AVERAGEX",as you wanna  correct the total value of the average result.

Check my sample .pbix file attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft, 

Thanks for working on this with me. I know you use dates to break down the average as shown below. I am not using any dates. Which I find your calculation to be a little harder. How can we replace your date calculation to just Names?

Your measure:

HL R12 CY = calculate (

AVERAGE('Datas Vol'[Total Volume]),

DATESBETWEEN( 'Dates'[date],

max( 'Dates'[Date])- 365,

max ('Dates'[Date])))

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Using values('Table[name]) instead,see below example:

Measure total = SUMX(VALUES('Table'[Name]),[Measure])

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!