Forum Discussion
Sum of Average in Matrix Table
I'm trying to get the total of the average by ProductGroup (eg 1.13 + 2.26 = 3.57). If I change the 'Values' to Average of 'Avg Step CT' in the vizualization setting. I get the right value for the rows within each Product Group. But the Product Group also Averages 'Avg Step CT'. I want the Product Group row - SPH, SPK etc to show the sum of all the rows instead of average. How can I achieve this?
Any ideas would be greatly appreciated!
Hi sitemarket ,
I have created a simple sample, please refer to it to see if it helps you.
Add an index column in Power Query first.
Then create a measure based on the Average of avg step ct.
I'll replace [Average of avg step ct] with [measure_max] here
result = VAR _b = SUMMARIZE ( 'Table', 'Table'[Index], "aaa", [measure_max] ) RETURN IF ( ISINSCOPE ( 'Table'[Index] ), [measure_max], SUMX ( _b, [aaa] ) )If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
I want the values to sum up
Please tell us if ypou have found the solution, I am currently in the same boat, unable to find the Sum of the values, where my values are the averages.
Hello.. if I can recall. I transformed the data in Power Query by doing the average by sub-group then visualize it as a sum in the dashboard.
- lbendlin
Super User
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Hi sitemarket ,
I have created a simple sample, please refer to it to see if it helps you.
Add an index column in Power Query first.
Then create a measure based on the Average of avg step ct.
I'll replace [Average of avg step ct] with [measure_max] here
result = VAR _b = SUMMARIZE ( 'Table', 'Table'[Index], "aaa", [measure_max] ) RETURN IF ( ISINSCOPE ( 'Table'[Index] ), [measure_max], SUMX ( _b, [aaa] ) )If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
