fixed margin for specific product type

[Anonymous]

Hi!

I am new to PBI and already have a challenge that I have been struggling with. 

I want to set the profit margin to always be 10% for every sales order line that has "cleaning service" as product type, regardless of what the actual margin would be. For any other product type / sales order line the margin should remain what it is. 

Sample Data:

Sales Order Number	Product Type	Sales	Profit	Profit%
1000	SUV 1	4000	1500	38%
1000	Cleaning Service	500	100	20%
Total		4500	1600	36%

Should turn in:

Sales Order Number	Product Type	Sales	Adj. Profit	Adj Profit%
1000	SUV 1	4000	1500	38%
1000	Cleaning Service	500	50	10%
Total		4500	1550	34%

The solution has to be a dax measure as I don't have access to the source / can't add columns. 

I already tried: 

Adjusted Profit= IF('Product Type' = "Cleaning Service", 'Sales'*0.1, Profit)

Thank you!

[Anonymous]

Thank you Arul!!

[Arul · Accepted solution]

Hi @Anonymous ,

Try to cretae two calculated columns to get the result.

New Profit % = IF(and(Profit[Product Type] = "Cleaning Service",Profit[Profit%]>0.1),0.1,Profit[Profit%])

.

New Profit = IF(Profit[Product Type]="Cleaning Service",DIVIDE( Profit[New Profit %]*Profit[Profit ],Profit[Profit%]),Profit[Profit ])

Use those two columns in your report and you will get your desired output.

Thanks,

Arul