Measure to subtract percentage from another measure

[Anonymous]

Hi PB-I community,

Does someone know how to create a Measure that calcutates a value by substrating a percentage?

So.. I have a column "SaleQty" in my Table that contains the quantity of items sold <type: "123" whole number>

I have a 2nd column "EndUserItemPrice" that contains the end-user price per item. <type:" $" fixed decimal number>

and a 3rd column "DealerDiscountPct" that contains the dealer discount percentage but stored as a number <type: "123" whole number>,  not as percentage.

I created a measure "EndUserSalesAmount" that multiplies "SaleQty" x "EndUserItemPrice" that works fine.

Now I need to substract the dealer discount "DealerDiscountPct" from this "EndUserSalesAmount"

For instance:

"SaleQty"= 2 items

"EndUserItemPrice"= $50

=> EndUserSalesAmount Measure = SUMX ( Sales, Sales[SaleQty] * Sales[EndUserItemPrice])

=> 2*50= 100

"DealerDiscountPct"= 10

I'm stuck on the new measure "DealerSalesAmount" to subtract 10 percent from the "EndUserSalesAmount". The outcome should be $100 - 10% = $90 Thx in advance!

G.

[harshnathani]

Hi @Anonymous ,

Then just subtract since it is a whole number.

Create a measure

DealerSalesAmount = [EndUserSalesAmount Measure]  - MAX('Table'[DealerDiscountPct])

where 

[EndUserSalesAmount Measure]  =   SUMX ( Sales, Sales[SaleQty] * Sales[EndUserItemPrice]) 

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @harshnathani 

Thx for your quick reply! 

However I'm afraid your measure won't work because I just can't subtract the number (of percentage) because it doens't represent a percentage.

I tried to change the type from <123 Whole Number> to <% Percentage> in Query Editor, but then it changes the number from f.i. 25 to 2500, so I diveded all by 100, so now I've got 25% in the Sales[DealerDiscountPct] column.

I hope I'm getting closer to the final measure, but still struggeling how to create a measure that 

DealerSalesAmount = [EndUserSalesAmount Measure] ......?????  substracts the Sales[DealerDiscountPct]

Thx

G.

[harshnathani]

Hi @Anonymous ,

My bad. I misread some details earlier.

I think this is what you are looking for.

EndUserSalesAmount Measure = SUMX ('Table', 'Table'[End User Item Price]*'Table'[Units])

Dealers Sale Amount = [EndUserSalesAmount Measure] *(1-MAX('Table'[Discount]))

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , Try like

EndUserSalesAmount Measure = SUMX ( Sales,(Sales[SaleQty] * Sales[EndUserItemPrice]) * (100 -Sales[DealerDiscountPct]))

[Anonymous]

Hi  @amitchandak 

Please note I don't need to calculate the EndUserSalesAmount as this number is known.

I need to calculate the  DealerSalesAmount by subtracting the DealerDiscountPct from the EndUserSalesAmount.

[amitchandak]

@Anonymous , I think that is what I have given

SUMX ( Sales,(Sales[SaleQty] * Sales[EndUserItemPrice]) * (100 -Sales[DealerDiscountPct]))