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
Miami_305
Frequent Visitor

Aggregate custom measure to use in another custom measure

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

A.png

 

 

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-

 

1 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:

41.PNG

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.

View solution in original post

5 REPLIES 5
v-joesh-msft
Solution Sage
Solution Sage

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:
11.PNG
Here is a demo, please try it:
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.
 

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.

B.png

 

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

 

Thanks in advance for your help

 

Cheers,

 

J

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:

41.PNG

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.

Thanks a lot.

 

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

 

Cheers,

 

J

amitchandak
Super User
Super User

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

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.