topic Re: Sum values in column instead of calculation in DAX Commands and Tips

Sum values in column instead of calculation

KMadsen — Fri, 23 Sep 2022 10:45:15 GMT

Hi all,

I have an issue trying to get the sum of the values in a measure. In the below screenshot, I want to be able to get the sum of the values in the column Weighted MAE% (33.1% and not 37.8%). In the total line I only get the calculation result for the total level (51/135) and the weights of each row is not considered.

How do I create a measure or column where I can the summed total for the weighted calculation in each row? I need to be able to understand the MAE% on aggregated levels considering the weight for each row and it has to be dynamic so that if I change the slicer to exclude e.g. Region 1, then it recalculates with new weights for each row.

Hope someone an help...

Re: Sum values in column instead of calculation

Greg_Deckler — Fri, 23 Sep 2022 10:47:51 GMT

KMadsen This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Re: Sum values in column instead of calculation

Jihwan_Kim — Fri, 23 Sep 2022 10:50:13 GMT

Hi,

I am not sure how your datamodel looks like, but please try to write a new measure like below, and put the below measure into the visualization.

MEASURE totalfix = SUMX ( VALUES ( 'TableName'[Region] ), [Weighted MAE%] )

Re: Sum values in column instead of calculation

KMadsen — Fri, 23 Sep 2022 12:18:18 GMT

Thanks for the suggestion Jihwan_Kim . Unfortunately, it does not yield the right result when I use the filters to select and deselect certain region or DGs. Is there a wa to make it dependent on all filters?

Re: Sum values in column instead of calculation

Jihwan_Kim — Fri, 23 Sep 2022 12:25:43 GMT

Hi,

Thank you for your feedback.

Could you please share your sample pbix file's link here? (Onedrive, googledrive, dropbox, any other...)

And then I can look into it to come up with a more accurate solution.

Or, please try the below if it is realted to two columns....

MEASURE totalfix = SUMX ( SUMMARIZE ( TableName, 'TableName'[Region], 'TableName'[DG] ), [Weighted MAE%] )

Re: Sum values in column instead of calculation

KMadsen — Fri, 23 Sep 2022 13:11:34 GMT

Thanks Jihwan_Kim , the summarize did the job! And it also works on more than 2 columns in the actual data set 🙂