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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Osti2
Regular Visitor

Dax formula to calculate comission

Hi guys! 

I need a little help about DAX formula to calculate comission for some items.

2 tables:

Commission Table

ID

FAMILY

PRODUCT

COMISSION

1

100

*

10%

2

100

CAR

5%

3

200

*

6%

4

300

*

7%

 

FACT TABLE

ID

FAMILY

PRODUCT

Euros

COMISSION

Total Com

1

100

MOTO

100

10%

10

2

300

APPLE

200

7%

14

3

200

HOUSE

300

6%

18

4

100

CAR

400

5%

20



Last two columns for FACT Table are calculated by DAX FORMULA

I need a formula with DAX that:

1.- Search if there's a comission (=FAMILY and =PRODUCT).
2.- If there's no comission, search for only one variable (=FAMILY and Product =*).

Do you know how to do it?

8 REPLIES 8
Anonymous
Not applicable

Hi @Osti2!

 

What result do you want, when you search?

Hi Again!

 

Example:

 

FACT Table ID1 : Family 100 and Product MOTO --> Search default comission (100 and *) = 10%

FACT Table ID1 : Family 100 and Product CAR --> Search specific comission (100 and CAR) = 5%

 

Anonymous
Not applicable

So instead of having duplicates of one family, you want the aggregated, is that correctly understood?

Exactly! it's like switch formula:

 

1.- Find family and product

2.-Find family and product *

 

Hi @Osti2,

 

Please refer below steps to get the desired results:

 

1. Create a custom Column in COMMISION table 

    Com_Key = IF(Commision[PRODUCT]="*",Commision[FAMILY],CONCATENATE(Commision[FAMILY],Commision[PRODUCT]))

Commission tableCommission table

2. Create a Custom Column in FACT table 

    FAct_Key = CONCATENATE('Fact'[FAMILY],'Fact'[PRODUCT])

 

3. Create a Custom Column in FACT table 

    Com = IF(ISBLANK(LOOKUPVALUE(Commision[COMISSION],Commision[Com_Key],'Fact'[FAct_Key])),LOOKUPVALUE(Commision[COMISSION],Commision[Com_Key],'Fact'[FAMILY]),LOOKUPVALUE(Commision[COMISSION],Commision[Com_Key],'Fact'[FAct_Key]))

 

Fact TableFact Table

Hope this helps!

 

-Sumit 

Thank you sumi4732!

 

Your solution works, but i'm looking more effective solution when comission is calculated by more than two variables.

 

I mean, imagine the same example but you are working with five variables:

 

-Family

-Product

-Item

-Campaing

-Store

 

With IF and LOOKUPVALUE is a bit difficult, don't you think?

Anonymous
Not applicable

@Osti2

 

I'm not sure what you want, which is different from what you already have. @sumit4732's solution gives you the same as you've already got. Maybe you should aggregate the families and their comissions? Or maybe I misunderstand what you want. Could you please elaborate?

 

Anonymous
Not applicable

Can you send me a picture of your desired end result?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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