topic Re: Divide a value by the value of that product with biggest value on other column in DAX Commands and Tips

Divide a value by the value of that product with biggest value on other column

Anonymous — Tue, 16 May 2023 14:08:20 GMT

Hello,

I have the following table with some measures in it at Product level:

I need a measure that will divide every value from Avg Price by the Avg Price of that Product with the biggest MS CY.

Example: for each product every value from AVG Price will be divided by 166.35.

I created the following measure to know which Product has the biggest MS CY:

MAX MS = FORMAT( MAXX(ALL('All'[PRODUCT DESCRIPTION]),[MS Butoane SKU]),"General Number")

The result beeing 26%, but I don't know how to substract AVG Price (166.35) of that Product.

Re: Divide a value by the value of that product with biggest value on other column

tamerj1 — Tue, 16 May 2023 14:45:31 GMT

Hi Anonymous

please try

Result =
DIVIDE (
[Avg Price],
MAXX (
SELECTCOLUMNS (
TOPN ( 1, ALL ( 'All'[PRODUCT DESCRIPTION] ), [MS Butoane SKU] ),
"@Avg", [Avg Price]
),
[@Avg]
)
)

Re: Divide a value by the value of that product with biggest value on other column

Anonymous — Wed, 17 May 2023 11:01:26 GMT

Is not working. It gives me 100% for every product.

Re: Divide a value by the value of that product with biggest value on other column

tamerj1 — Wed, 17 May 2023 12:07:40 GMT

Hi Anonymous
Please try

Result = DIVIDE ( [Avg Price], MAXX ( SELECTCOLUMNS ( ALL ( 'All'[PRODUCT DESCRIPTION] ), "@Avg", CALCULATE ( [MS Butoane SKU], ALLEXCEPT ( 'All', 'All'[PRODUCT DESCRIPTION] ) ) ), [@Avg] ) )

Re: Divide a value by the value of that product with biggest value on other column

Anonymous — Wed, 17 May 2023 12:26:36 GMT

This is the result I need:

Avg Price for every product needs to be divided by the Avg Price of that Product with the highest MS.

In this example, the highest MS is 26%, so every row will be divided by 166.346. On row 33 will have 100% because it will divide 166.346 by 166.346.

Depending on the slicer filter with category, the highest MS for that category will change.

Re: Divide a value by the value of that product with biggest value on other column

tamerj1 — Wed, 17 May 2023 13:38:51 GMT

Anonymous
Please try

Result = DIVIDE ( [Avg Price], MAXX ( TOPN ( 1, ALL ( 'All'[PRODUCT DESCRIPTION] ), CALCULATE ( [MS Butoane SKU], 'All'[PRODUCT DESCRIPTION] = 'All'[PRODUCT DESCRIPTION] ) ), CALCULATE ( [Avg Price], 'All'[PRODUCT DESCRIPTION] = 'All'[PRODUCT DESCRIPTION] ) ) )

Re: Divide a value by the value of that product with biggest value on other column

tamerj1 — Wed, 17 May 2023 17:12:40 GMT

Hi Anonymous
I do apologize as I was answering in a harry without paying much attention. Somehow your question just popped up in my head and I realized I did a stupid mistake in my last code. Please try the following instead:

Result = DIVIDE ( [Avg Price], MAXX ( TOPN ( 1, ALLSELECTED ( 'All'[PRODUCT DESCRIPTION] ), VAR CurrentProduct1 = 'All'[PRODUCT DESCRIPTION] RETURN CALCULATE ( [MS Butoane SKU], 'All'[PRODUCT DESCRIPTION] = CurrentProduct1 ) ), VAR CurrentProduct2 = 'All'[PRODUCT DESCRIPTION] RETURN CALCULATE ( [Avg Price], 'All'[PRODUCT DESCRIPTION] = CurrentProduct2 ) ) )

Re: Divide a value by the value of that product with biggest value on other column

Anonymous — Thu, 18 May 2023 06:14:51 GMT

Thank you tamerj1 didn't know you can put variable in the middle of the measure.