Hi guys!
I need a little help about DAX formula to calculate comission for some items.
2 tables:
Commission Table
[TABLE="width: 320"]
<colgroup><col width="80" span="4" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 80"]ID[/TD]
[TD="class: xl65, width: 80"]FAMILY[/TD]
[TD="class: xl65, width: 80"]PRODUCT[/TD]
[TD="class: xl65, width: 80"]COMISSION[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]*[/TD]
[TD="class: xl66"]10%[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]CAR[/TD]
[TD="class: xl66"]5%[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]*[/TD]
[TD="class: xl66"]6%[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]*[/TD]
[TD="class: xl66"]7%[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 320"]
<colgroup><col width="80" span="4" style="width:60pt"></colgroup><tbody>[TR]
[TD="class: xl65"][TABLE="width: 320"]
<colgroup><col width="80" span="4" style="width:60pt"></colgroup><tbody>[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]
FACT TABLE
[TABLE="width: 480"]
<colgroup><col width="80" span="6" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 80"]ID[/TD]
[TD="class: xl66, width: 80"]FAMILY[/TD]
[TD="class: xl66, width: 80"]PRODUCT[/TD]
[TD="class: xl66, width: 80"]Euros[/TD]
[TD="class: xl66, width: 80"]COMISSION[/TD]
[TD="class: xl66, width: 80"] Total Com[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]MOTO[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl67"]10%[/TD]
[TD="class: xl68"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]APPLE[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl67"]7%[/TD]
[TD="class: xl68"]14[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]HOUSE[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl67"]6%[/TD]
[TD="class: xl68"]18[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]CAR[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl67"]5%[/TD]
[TD="class: xl68"]20[/TD]
[/TR]
</tbody>[/TABLE]
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?
I need a little help about DAX formula to calculate comission for some items.
2 tables:
Commission Table
[TABLE="width: 320"]
<colgroup><col width="80" span="4" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 80"]ID[/TD]
[TD="class: xl65, width: 80"]FAMILY[/TD]
[TD="class: xl65, width: 80"]PRODUCT[/TD]
[TD="class: xl65, width: 80"]COMISSION[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]*[/TD]
[TD="class: xl66"]10%[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]CAR[/TD]
[TD="class: xl66"]5%[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]*[/TD]
[TD="class: xl66"]6%[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]*[/TD]
[TD="class: xl66"]7%[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 320"]
<colgroup><col width="80" span="4" style="width:60pt"></colgroup><tbody>[TR]
[TD="class: xl65"][TABLE="width: 320"]
<colgroup><col width="80" span="4" style="width:60pt"></colgroup><tbody>[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]
FACT TABLE
[TABLE="width: 480"]
<colgroup><col width="80" span="6" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 80"]ID[/TD]
[TD="class: xl66, width: 80"]FAMILY[/TD]
[TD="class: xl66, width: 80"]PRODUCT[/TD]
[TD="class: xl66, width: 80"]Euros[/TD]
[TD="class: xl66, width: 80"]COMISSION[/TD]
[TD="class: xl66, width: 80"] Total Com[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]MOTO[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl67"]10%[/TD]
[TD="class: xl68"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]APPLE[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl67"]7%[/TD]
[TD="class: xl68"]14[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]200[/TD]
[TD="class: xl65"]HOUSE[/TD]
[TD="class: xl65"]300[/TD]
[TD="class: xl67"]6%[/TD]
[TD="class: xl68"]18[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]CAR[/TD]
[TD="class: xl65"]400[/TD]
[TD="class: xl67"]5%[/TD]
[TD="class: xl68"]20[/TD]
[/TR]
</tbody>[/TABLE]
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?