cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Assigning message "low margin" or "negative margin" based on margin% given certain order types

I have three order types based on "order" being with external customer (order type ZS02) or internal customer (order types ZS03, ZS04). I'd like to assign the legend "low margin" (on new column) based on pre-defined threshold values  (per order type), or assign legend "negative margin" if the return value is -in fact- negative. The mentioned thresholds are 12% or less than 12% for order type ZS02, 7.5% or less for order type ZS03, and 6.4% or less for order type ZS04. Below a mock table of the final -desired- output (last column would be the column with the legend). How can this be accomplished?

 

OrderOrder TypeMarginText assignment ( new column)
azs02-1%Negative Margin
bzs032%Low margin
czs043%Low margin
dzs039% 
ezs0410% 
fzs0210%Low margin
gzs03-4%Negative margin
hzs0216% 
izs045%Low margin

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Assigning message "low margin" or "negative margin" based on margin% given

Perhaps:

 

 

Column =
  SWITCH(TRUE(),
    [Margin] < 0,"Negative margin",
    [Margin] <= .12 && [Order Type] = "zs02","Low margin",
    [Margin] <= .075 && [Order Type] = "zs03","Low margin",
    [Margin] <= .064 && [Order Type] = "zs04","Low margin",
    BLANK()
  )     

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Assigning message "low margin" or "negative margin" based on margin% given

Perhaps:

 

 

Column =
  SWITCH(TRUE(),
    [Margin] < 0,"Negative margin",
    [Margin] <= .12 && [Order Type] = "zs02","Low margin",
    [Margin] <= .075 && [Order Type] = "zs03","Low margin",
    [Margin] <= .064 && [Order Type] = "zs04","Low margin",
    BLANK()
  )     

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Community Support
Community Support

Re: Assigning message "low margin" or "negative margin" based on margin% given

Hi @zuro16

 

You need a measure as below:

 

Measure = IF(SELECTEDVALUE('Table'[Margin])>0,
SWITCH(SELECTEDVALUE('Table'[Order Type]),
"ZS02",IF(SELECTEDVALUE('Table'[Margin])<=0.12,"low margin",BLANK()),
"ZS03",IF(SELECTEDVALUE('Table'[Margin])<=0.075,"low margin",BLANK()),
 "ZS04",IF(SELECTEDVALUE('Table'[Margin])<=0.064,"low margin",BLANK()),
 BLANK()),
 "negative margin")

 

Finally,you will see:

 

Annotation 2020-03-12 145027.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Highlighted
New Member

Re: Assigning message "low margin" or "negative margin" based on margin% given

Thanks for this, but id did not work for me...it said something about the (selected value) being limited only to two tries in the formula...it worked for me with the other suggested solution

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors