Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
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.
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])
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
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:
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
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
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |