DAX Help

[jerryz636]

Hi im new to DAX and hopign to get help with the following. Im trying to develop a way to see how much actual money a salesman gets paid based on the type of product that is sold. I sell Books and Drinks. If a book is sold we pay out at 3 levels based on margin%. same for drinks, although drinks have a different payout structure. So the higher a margin% the more money they get paid out. 

If 

Table(ProductType)="Books" and Table(Margin%) <15 then Table(Margin)*0

Table(ProductType)="Books" and Table(Margin%) >15.01 and <25  then Table(Margin)*.25

Table(ProductType)="Books" and Table(Margin%) >25.01 then Table(Margin)*.35

Table(ProductType)="Drinks" and Table(Margin%) <25 then Table(Margin)*0

Table(ProductType)="Drinks" and Table(Margin%) >25.01 and < 35 then Table(Margin)*.35

Table(ProductType)="Drinks" and Table(Margin%) >35.01 then Table(Margin)*.45

[v-cazheng-msft]

Hi, @jerryz636 

I think you can refer the dax formula from Tahreem24 to create a Measure. And just make some changes to the dax formula.

Measure =
SWITCH (
TRUE (),
Table[ProductType] = "Books"&& Table[Margin%] <15, Table[Margin%] * 0,
Table[ProductType] = "Books"&& Table[Margin%] > 15.01&& Table[Margin%] <25, Table[Margin%] *0 .25,
Table[ProductType] = "Books"&& Table[Margin%] > 25.01, Table[Margin%] *0 .35,
Table[ProductType] = "Drinks"&& Table[Margin%] <25, Table[Margin%] * 0,
Table[ProductType] = "Drinks"&& Table[Margin%] > 25.01&& Table[Margin%] <35, Table[Margin%] * 0.35,
Table[ProductType] = "Drinks"&& Table[Margin%] > 35.01, Table[Margin%] * 0.45
)

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tahreem24 · Accepted solution]

Create a measure using Switch like below:

Measure = SWITCH(True(), 

Table(ProductType)="Books" && Table(Margin%) <15 ,Table(Margin)*0, 

Table(ProductType)="Books" && Table(Margin%) >15.01 && <25, Table(Margin)*.25, 

Table(ProductType)="Books"  && Table(Margin%) >25.01, Table(Margin)*.35, 

Table(ProductType)="Drinks" && Table(Margin%) <25, Table(Margin)*0, 

Table(ProductType)="Drinks" && Table(Margin%) >25.01 && < 35 ,Table(Margin)*.35, 

Table(ProductType)="Drinks" &&Table(Margin%) >35.01 ,Table(Margin)*.45) 

[jerryz636]

I rewrote that part about the < function to like this 

'OE Invoices'[ProductPriceTypeID]="LIQ" && 'Calendar'[Profit Margin] >14.802 && 'Calendar'[Profit Margin]<24.801, 'Calendar'[Total Margin]*.275, and got past that. But my issue is now with [ProductPriceTypeID]. I get a single value for column "ProductPriceTypeID' in tabe 'OE Invocies' cannot be determined.   Any thoughts on what i need to do to get past this hurdle? not sure if my field needs to be changed?

[jerryz636]

HI and thanks,  i rewrote my statement and get a message, the syntax for '<" is incorrect. This is my exact usage. 

'OE Invoices'[ProductPriceTypeID]="LIQ" && 'Calendar'[Profit Margin] >14.802 && <24.801, 'Calendar'[Total Margin]*.275,