Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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%
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]))
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]))
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?
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?
Can you send me a picture of your desired end result?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |