Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to create multiple criteria from one measure value

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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
Not applicable

thank you it worked

amitchandak
Super User
Super User

@Anonymous , Use Switch true, A new measure

 

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Hi Amitchandak,

 

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

 

Laedays_0-1612783207491.png

 

@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"
)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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.

 

Laedays_1-1612784102629.png

 

 

 

 

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Can you help me with the code?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors