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

[Anonymous]

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.

[tamerj1 · Accepted solution]

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 )
    )
)

[Anonymous]

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

[tamerj1]

Hi @Anonymous 

please try

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

[Anonymous]

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

[tamerj1]

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]
    )
)

[Anonymous]

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.

[tamerj1]

@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]
        )
    )
)