Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
souvik900766
Helper IV
Helper IV

Sumx not giving correct total

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
totalerror.png

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

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

Hi @AlexisOlson ,

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors