Aggregate custom measure to use in another custom measure

[Miami_305]

Hi all,

Im trying to sum result of "New 9L Volume Organic" to be used in another new measure.

So far I can do it if I use my data without any manipulation (column NEW 9L Volume House) but not successfull with the one using custom measure.

Thank you in advance for your help/comments

Cheers,

J

Below screenshoot

Below code

New 9L Volume Organic =

VAR

NEW_9L_VOL_All_SIZE = CALCULATE(SUM('07_Impact Table'[NEW 9L Volume]),ALL(Brand_Key[05 Size Name]))

VAR

OLD_9L_VOL_All_SIZE = CALCULATE(SUM('07_Impact Table'[OLD 9L Volume]),ALL(Brand_Key[05 Size Name]))

VAR

ORG_VS_LD = IF( or(

and(NEW_9L_VOL_All_SIZE<>0,OLD_9L_VOL_All_SIZE=0),

and(NEW_9L_VOL_All_SIZE=0,OLD_9L_VOL_All_SIZE<>0)

),

"L/D","Org")

RETURN

VAR

NEW_9L_VOL_ORG = CALCULATE(IF(ORG_VS_LD="ORG",NEW_9L_VOL_All_SIZE),ALL(Brand_Key[04 Brand Name]))

RETURN

NEW_9L_VOL_ORG-

[v-joesh-msft]

Hi @Miami_305 ,

This looks like a measure totals problem. Very common. See this post about it
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

For your case, You can create the following measure:

Measure = SUMX(VALUES('07_Impact Table'[Product]),[New 9L Volume Organic])

Results are as follows:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ET4eOzpGWFRJmp9CUz...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Miami_305]

Thanks Joesh but unfortunately, your suggestion did not work since what I'm trying to achieve is to have for all rows showed total amount of NEW Volume organic.

See below example desired.

What the formula you suggested did is to show a total in the total row.

Thanks in advance for your help

Cheers,

J

[v-joesh-msft · Accepted solution]

Hi @Miami_305 ,

You may need to create a new measure:

NEW_9L_VOL_All_SIZE =
CALCULATE (
    SUM ( '07_Impact Table'[NEW 9L Volume] ),
    ALL ( Brand_Key[05 Size Name] )
)

Then create the following measure:

ALL-New 9L Volume Organic = 
VAR NEW_9L_VOL_All_SIZE =
    CALCULATE (
        SUM ( '07_Impact Table'[NEW 9L Volume] ),
        ALL ( Brand_Key[05 Size Name] )
    )
VAR OLD_9L_VOL_All_SIZE =
    CALCULATE (
        SUM ( '07_Impact Table'[OLD 9L Volume] ),
        ALL ( Brand_Key[05 Size Name] )
    )
VAR ORG_VS_LD =
    IF (
        OR (
            AND ( NEW_9L_VOL_All_SIZE <> 0, OLD_9L_VOL_All_SIZE = 0 ),
            AND ( NEW_9L_VOL_All_SIZE = 0, OLD_9L_VOL_All_SIZE <> 0 )
        ),
        "L/D",
        "Org"
    )
RETURN
    CALCULATE([NEW_9L_VOL_All_SIZE],FILTER(ALL('07_Impact Table'),[ORG_VS_LD]="Org"))

Results are as follows:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EeQI1ELgAmdFkepjz9...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Miami_305]

Thanks a lot.

Even thought I did not use your calculation, the main idea of having another measure is what works!

Cheers,

J

[amitchandak]

New 9L Volume Organic is already aggregated, so you should use it as is.  Or Try using sumx.

https://docs.microsoft.com/en-us/dax/sumx-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601