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

fixed margin for specific product type

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 NumberProduct TypeSalesProfit Profit%
1000SUV 14000150038%
1000Cleaning Service50010020%
Total 4500160036%

 

Should turn in:

Sales Order NumberProduct TypeSalesAdj. ProfitAdj Profit%
1000SUV 14000150038%
1000Cleaning Service5005010%
Total 4500155034%

 

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!
1 ACCEPTED SOLUTION
Arul
Super User
Super User

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.

 

Arul_1-1651750720706.png

Thanks,

Arul

 





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

Proud to be a Super User!


LinkedIn


View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thank you Arul!!

Arul
Super User
Super User

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.

 

Arul_1-1651750720706.png

Thanks,

Arul

 





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

Proud to be a Super User!


LinkedIn


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.