Sumx not giving correct total

souvik900766 — Fri, 24 Dec 2021 18:04:06 GMT

Hi ,

I am getting the incorrect subcategory total.
e.g:-FSSD + COLA = 0.24 but coming 0.56. then JNS + Dairy =0.03 but coming 0.04
My correct sub category totals to form correct Total .

My file:- https://www.dropbox.com/s/4q6scqcfot9uj1k/totalerrror.pbix?dl=0

Its urgent , please help

Re: Sumx not giving correct total

Greg_Deckler — Fri, 24 Dec 2021 19:03:44 GMT

souvik900766 Try MM3TR&R: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

Re: Sumx not giving correct total

AlexisOlson — Fri, 24 Dec 2021 22:50:50 GMT

Your [Mix Impact] measure is complex but the issue is the typical subtotaling issue.

Mix Impact = VAR IndustryRNARTDBaseCY = [CY Ind RNARTD] VAR IndustryRNARTDBasePY = [PY Ind RNARTD] VAR INDRNARTD = SUMX ( VALUES ( Category[Category to be used] ), ( DIVIDE ( [CY Ind], IndustryRNARTDBaseCY ) - DIVIDE ( [PY Ind], IndustryRNARTDBasePY ) ) * 100 * [PY Value Share Withhin OU] ) VAR IndustryRNARTDCOLABaseCY = [CY Ind RNARTD] VAR IndustryRNARTDCOLABasePY = [PY Ind RNARTD] VAR IndRNARTDCOLA = SUMX ( VALUES ( 'Category'[Category to be used] ), ( DIVIDE ( [CY Ind COLA], IndustryRNARTDCOLABaseCY ) - DIVIDE ( [PY Ind COLA], IndustryRNARTDCOLABasePY ) ) * 100 * [PY Value Share Withhin OU Cola] ) VAR IndustryRNARTDFSSDBaseCY = [CY Ind RNARTD] VAR IndustryRNARTDFSSDBasePY = [PY Ind RNARTD] VAR IndRNARTDFSSD = SUMX ( VALUES ( 'Category'[Category to be used] ), ( DIVIDE ( [CY Ind FSSD], IndustryRNARTDFSSDBaseCY ) - DIVIDE ( [PY Ind FSSD], IndustryRNARTDFSSDBasePY ) ) * 100 * [PY Value Share Withhin OU Mixed] ) RETURN SWITCH ( TRUE (), SELECTEDVALUE ( Category[Category to be used] ) = "SSD Cola (Within SSD Cola)", IndRNARTDCOLA, SELECTEDVALUE ( Category[Category to be used] ) = "SSD Cola (Within SSD)", "", SELECTEDVALUE ( Category[Category to be used] ) = "FSSD (Within SSD Non-Cola)", IndRNARTDFSSD, SELECTEDVALUE ( Category[Category to be used] ) = "FSSD (Within SSD)", "", INDRNARTD )

For totals including more than one category, it always returns the INDRNARTD default option from the SWITCH.

The standard solution is to iterate over the granularity that you want to sum at. That is,

SUMX ( VALUES ( Category[Category to be used] ), [Mix Impact] )

However, this may not work for the same reason you had issues previously. I.e., you'd have the ALLSELECTED functions from [CY/PY Ind RNARTD] inside of an iterator.

I'm not making any guarantees that this will work but you might be able to refactor the measure along these lines:

Mix Impact = VAR IndustryRNARTDBaseCY = [CY Ind RNARTD] VAR IndustryRNARTDBasePY = [PY Ind RNARTD] VAR IndustryRNARTDCOLABaseCY = [CY Ind RNARTD] VAR IndustryRNARTDCOLABasePY = [PY Ind RNARTD] VAR IndustryRNARTDFSSDBaseCY = [CY Ind RNARTD] VAR IndustryRNARTDFSSDBasePY = [PY Ind RNARTD] RETURN SUMX ( VALUES ( Category[Category to be used] ), SWITCH ( Category[Category to be used], "SSD Cola (Within SSD Cola)", ( DIVIDE ( [CY Ind COLA], IndustryRNARTDCOLABaseCY ) - DIVIDE ( [PY Ind COLA], IndustryRNARTDCOLABasePY ) ) * 100 * [PY Value Share Withhin OU Cola], "FSSD (Within SSD)", "", "FSSD (Within SSD Non-Cola)", ( DIVIDE ( [CY Ind FSSD], IndustryRNARTDFSSDBaseCY ) - DIVIDE ( [PY Ind FSSD], IndustryRNARTDFSSDBasePY ) ) * 100 * [PY Value Share Withhin OU Mixed], "SSD Cola (Within SSD)", "", ( DIVIDE ( [CY Ind], IndustryRNARTDBaseCY ) - DIVIDE ( [PY Ind], IndustryRNARTDBasePY ) ) * 100 * [PY Value Share Withhin OU] ) )

Re: Sumx not giving correct total

souvik900766 — Tue, 28 Dec 2021 06:10:02 GMT

Hi AlexisOlson ,

Thanks a ton for the help !
It worked perfectly with guarantee !😎

topic Sumx not giving correct total in DAX Commands and Tips

Sumx not giving correct total

Re: Sumx not giving correct total

Re: Sumx not giving correct total

Re: Sumx not giving correct total