Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
diglesiasv
New Member

CALCULATE CUSTOMER BONUSES BY TYPE AND SALES AMOUNT

Hello,

I must calculate customer bonuses according to their type and level of sales. 

I need to show a table where you see the ID, Client, Type and Bonus.

Please help.

 

I have a table, Client with ID, Name and Type headers.

I have a measure " sales() " that calculates sales by adding invoices from another table

 

And I have the conditions:

 

If Client_Type="Retail" && sales>=10 && sales<20; 100

If Client_Type="Retail" && sales>=20 && sales<30; 110

If Client_Type="Retail" && sales>=30 && sales<40; 120

If Client_Type="Retail" && sales>=40; 130

 

If Client_Type="Shop" && sales>=15 && sales<25; 200

If Client_Type="Shop" && sales>=25 && sales<35; 210

If Client_Type="Shop" && sales>=35 && sales<45; 220

If Client_Type="Shop" && sales>=45; 230

 

If Client_Type="Web" && sales>=20 && sales<25; 300

If Client_Type="Web" && sales>=25 && sales<30; 310

If Client_Type="Web" && sales>=30 && sales<50; 320

If Client_Type="Web" && sales>=50; 330

 

0))))

 

For example:

 

ID           NAME     TYPE      SALES   BONUS

00001    Client1     Retail      45       130

00002    Client2     Shop       20       200

00003    Client3     Web       38        320

00004    Client4     Retail      4           0

 

 

Thank you

1 REPLY 1
amitchandak
Super User
Super User

@diglesiasv , Try a measure like.

Switch(True()
max(Client_Type)="Retail" && sum(sales)>=10 && sales<20; 100;
max(Client_Type)="Retail" && sum(sales)>=10 && sales<30; 110;
max(Client_Type)="Retail" && sum(sales)>=10 && sales<40; 120;
130
)

 

Use correct row context while using

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors