Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
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
thank you it worked
Hi Amitchandak,
Thanks for the suggestion. I tried to replicate the switch formula but error came up. See below:
@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"
)
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.
@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
Can you help me with the code?