Sumx not giving correct total

[souvik900766]

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

[AlexisOlson · Accepted solution]

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]
        )
    )

[souvik900766]

Hi @AlexisOlson ,

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

[Greg_Deckler]

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