Dax formula to calculate comission

[Osti2]

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?

[Anonymous]

Hi @Osti2!

What result do you want, when you search?

[Osti2]

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]

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

[Osti2]

Exactly! it's like switch formula:

1.- Find family and product

2.-Find family and product *

[sumit4732]

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 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 Table

Hope this helps!

-Sumit 

[Osti2]

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]

@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]

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