Help with DAX and SUMX, totals showing incorrectly

[robbo0]

Hi all,

I have a data model as shown in the below image, all directly or indirectly linked through a field called [%PLANT_MATERIAL].

The aim here is to create a matrix which shows the total demand 'APO Demand'[APO Demand] by week commencing at different levels of hierarchy (KNA1.NAME1 - Name 1, MARA.MSBOOKPARTNO - Family Group, MAKT.MAKTX - Material Description (Short Text) and MARA.MATNR - Material Number), however, depending on the work centre selected in the matrix or relevant chart in small multiples the value shown should take in to account the 'Work Centre'[UOM].

If the relevant %PLANT_MATERIAL goes through a work centre which has a [UOM] of 'SH' then the volume should be divided by 'Material Model'[PZVC_CART.F_NUMBERUP - Box WIP No Up]. The model knows which work centre the material passes through based on the 'Routes' table.

The struggle I am having is getting the total of each week commencing to correctly display, which is leading to the charts showing overall volume vs capacity not displaying correctly. 

Below is the DAX I am currently using...

APO Demand SH =

DIVIDE(

    SUM('APO Demand'[APO Demand]),

    MAX('Material Model'[PZVC_CART.F_NUMBERUP - Box WIP No Up])

    )

RCCP Demand =

SUMX(VALUES('Material Model'[MARA.MSBOOKPARTNO - Family Group]),

        IF(

            MAX('Work Centre'[UOM]) = "SH",

            'APO Demand'[APO Demand SH],

    SUM('APO Demand'[APO Demand])

))

Thanks in advance. O

[123abc · Accepted solution]

It seems like you're trying to calculate demand based on different levels of hierarchy while considering the unit of measure (UOM) and adjusting the demand accordingly if it passes through a specific work center with UOM 'SH'. Here's how you can adjust your DAX measures to correctly calculate the total demand by week commencing:

1. APO Demand SH: This measure calculates the demand, taking into account the 'SH' unit of measure if applicable.

APO Demand SH =
IF (
SELECTEDVALUE ( 'Work Centre'[UOM] ) = "SH",
DIVIDE (
SUM ( 'APO Demand'[APO Demand] ),
MAX ( 'Material Model'[PZVC_CART.F_NUMBERUP - Box WIP No Up] )
),
SUM ( 'APO Demand'[APO Demand] )
)

RCCP Demand: This measure calculates the demand for each family group, considering whether it passes through a work center with UOM 'SH' or not.

RCCP Demand =
SUMX (
VALUES ( 'Material Model'[MARA.MSBOOKPARTNO - Family Group] ),
[APO Demand SH]
)

Make sure to replace the measure [APO Demand SH] in the RCCP Demand measure with the adjusted version I provided above. This way, the RCCP Demand measure will sum up the demand correctly for each family group, considering the adjusted demand based on the unit of measure.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[v-shex-msft · Accepted solution]

Hi @robbo0 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[123abc · Accepted solution]

It seems like you're trying to calculate demand based on different levels of hierarchy while considering the unit of measure (UOM) and adjusting the demand accordingly if it passes through a specific work center with UOM 'SH'. Here's how you can adjust your DAX measures to correctly calculate the total demand by week commencing:

1. APO Demand SH: This measure calculates the demand, taking into account the 'SH' unit of measure if applicable.

APO Demand SH =
IF (
SELECTEDVALUE ( 'Work Centre'[UOM] ) = "SH",
DIVIDE (
SUM ( 'APO Demand'[APO Demand] ),
MAX ( 'Material Model'[PZVC_CART.F_NUMBERUP - Box WIP No Up] )
),
SUM ( 'APO Demand'[APO Demand] )
)

RCCP Demand: This measure calculates the demand for each family group, considering whether it passes through a work center with UOM 'SH' or not.

RCCP Demand =
SUMX (
VALUES ( 'Material Model'[MARA.MSBOOKPARTNO - Family Group] ),
[APO Demand SH]
)

Make sure to replace the measure [APO Demand SH] in the RCCP Demand measure with the adjusted version I provided above. This way, the RCCP Demand measure will sum up the demand correctly for each family group, considering the adjusted demand based on the unit of measure.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.