Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
95 | |
75 | |
63 | |
62 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
66 |