Forum Discussion

kilala's avatar
kilala
Resolver I
4 years ago
Solved

How to do Calculation Based on Total Measure

Hi all,

 

I have a measure named Amount Excluded derived from measure named Flag. 

The logic for Flag Measure is:

Flag = 

IF (
SELECTEDVALUE(DimProduct[ProductName]) = "A",
"2",
IF(
[Original Amount]>0,
"0","1")
))
 
The logic for Amount Measure is:
SUMX(
SUMMARIZE(vw_DimProduct,vw_DimProduct[ProductName],"Amt Exclude",
IF([Identifier] = "1",[Original Amount],BLANK())),[Amt Exclude])
 
p/s: I am able to get total -7000 when I use SUMX. 
 
I want to create another measure, named Adjusted Amount where the calculation is based on total Amount Excluded. Example of calculation is as follow:

ProductOriginal AmountFlagAmount ExcludedAdjusted Amount
A10002  
B010 
C-50001-5000 
D-20000-2000 
E30000 = (-7000/2)+3000
F10000 = (-7000/2)+1000
TOTAL9000 -7000 

 

But currrently, I cannot do that, it detect Amount Excluded as Blank instead of -7000. I think because it treat by line, in Row  and F, Amount Excluded is Blank, that's why.
 
How do I ask it to take value -7000?
I checked other queries but none of it works on my case. 
Looking forward to hear from you guys!!
measure

2 Replies

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    4 years ago

    Hi kilala 

    See if this gives you the -7000.

    CALCULATE(
    [Amount Excluded],
    ALLSELECTED()
)