Forum Discussion

Newbie_1's avatar
Newbie_1
Frequent Visitor
1 year ago

Dynamically calculating an index with a fixed value based on selection criteria

Hi there, 

I am doing a pricing analysis in EUR/L, and I would like to calculate to do an indexation of all products within a certain category where the base value for all products should always be the EUR/L price for the product with the highest % market share or top volume rank. 

Please refer to the below table:
- EUR/L comes from the data table

- % Market share is from the data table

- For volume rank I use the following measure : RANKX(ALLSELECTED('EUR/L Overview'),CALCULATE(SUM('RSP Overview'[Volumes])))

- The intention for "EUR/L for product with highest market share" in this case, is supposed to generate 55.64 across all rows in the table, so that I am eventually able to do the indexation. I have been trying various DAX calculations with 'FILTER', 'MAX', 'CALCULATE' but it's either going against the system logic with true/false expressions, or generates the below output (the EUR/L for the respective product). Any suggestions how I can achieve the desired result?

 

Much appreciate any help

1 Reply

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    Newbie_1 , Try usinge below DAX

     

    Calculate the EUR/L for the product with the highest market share:

    HighestMarketShareEUR_L =
    CALCULATE(
    MAX('EUR/L Overview'[EUR/L]),
    FILTER(
    'EUR/L Overview',
    'EUR/L Overview'[% Market Share] = MAXX(ALL('EUR/L Overview'), 'EUR/L Overview'[% Market Share])
    )
    )


    Calculate the EUR/L for the product with the top volume rank:
    TopVolumeRankEUR_L =
    CALCULATE(
    MAX('EUR/L Overview'[EUR/L]),
    FILTER(
    'EUR/L Overview',
    RANKX(ALLSELECTED('EUR/L Overview'), CALCULATE(SUM('RSP Overview'[Volumes]))) = 1
    )
    )


    Choose the appropriate EUR/L value based on your criteria (highest market share or top volume rank):
    BaseEUR_L =
    IF(
    [Use Market Share],
    [HighestMarketShareEUR_L],
    [TopVolumeRankEUR_L]
    )


    Create the indexation measure:
    IndexedEUR_L =
    DIVIDE(
    'EUR/L Overview'[EUR/L],
    [BaseEUR_L]
    )

    [Use Market Share] is a boolean measure or parameter that you can set to determine whether to use the highest market share or the top volume rank for the base EUR/L value.