How to create multiple criteria from one measure value

[Anonymous]

I have one measure column that contains positive and negative numbers. Is there a way to extract 3 attribute label out of 1 measure column? For example,

IF [Measure Value] > 0 THEN "Cost",

Else If [Measure Value] < 0 THEN "Savings",

Else "Total Savings" 

Total Savings should be the Sum of Cost and Savings.

I should have filter containing Cost, Savings and Total Savings.

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

According to the below logic, it will return the value as "Total Savings" only when [Measure Value] is equal to 0. But you also mentioned that Total Savings should be the Sum of Cost and Savings. So how about the returned value  when [Measure Value] is equal to 0?

---

IF [Measure Value] > 0 THEN "Cost",

Else If [Measure Value] < 0 THEN "Savings",

Else "Total Savings" 

---

Please try to create a calculated column with the below formula and check whether it can get what you want.

Metrics =
IF (
    [Measure Value] = [Cost] + [Savings],
    "Total Savings",
    IF ( [Measure Value] > 0, "Cost", "Savings" )
)

If the above one is not what you want, please provide the formula of measure [Measure Value], some sample data and your final result with details. Thank you.

Best Regards

[Anonymous]

thank you it worked

[amitchandak]

@Anonymous , Use Switch true, A new measure

Switch( True(),
[Measure Value] > 0 , "Cost",
[Measure Value] < 0 ,"Savings",
"Total Savings"
)

[Anonymous]

Hi Amitchandak,

Thanks for the suggestion. I tried to replicate the switch formula but error came up. See below:

[amitchandak]

@Anonymous , where  you have tried this in a new measure like ?

New measure = SWITCH ( True(),
[Measure Value] > 0 , "Cost",
[Measure Value] < 0 ,"Savings",
"Total Savings"
)

[Anonymous]

I was able to add the formula but it only shows Cost and Savings. The Total Savings does not appear. My goal if possible is to create a slicer that contains the three: Cost, Savings and Total Savings.

[amitchandak]

@Anonymous , To Create a slicer on the measure you have to dynamic segmentation or binning using an independent table

refer my video : https://youtu.be/CuczXPj0N-k

or these https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Anonymous]

Can you help me with the code?