topic Re: Measure to subtract percentage from another measure in DAX Commands and Tips

Measure to subtract percentage from another measure

Anonymous — Fri, 17 Jul 2020 12:48:59 GMT

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!

Re: Measure to subtract percentage from another measure

amitchandak — Fri, 17 Jul 2020 12:54:32 GMT

Anonymous , Try like

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

Re: Measure to subtract percentage from another measure

Anonymous — Fri, 17 Jul 2020 13:05:32 GMT

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!

Re: Measure to subtract percentage from another measure

Anonymous — Fri, 17 Jul 2020 14:55:10 GMT

Hi Anonymous

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

Re: Measure to subtract percentage from another measure

Anonymous — Fri, 17 Jul 2020 15:09:18 GMT

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.

Re: Measure to subtract percentage from another measure

amitchandak — Fri, 17 Jul 2020 15:50:53 GMT

Anonymous , I think that is what I have given

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

Re: Measure to subtract percentage from another measure

Anonymous — Fri, 17 Jul 2020 18:18:19 GMT

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!