topic fixed margin for specific product type in DAX Commands and Tips

fixed margin for specific product type

Anonymous — Wed, 04 May 2022 07:41:31 GMT

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!

Re: fixed margin for specific product type

Arul — Thu, 05 May 2022 11:39:33 GMT

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

Re: fixed margin for specific product type

Anonymous — Thu, 05 May 2022 12:58:03 GMT

Thank you Arul!!