topic Dynamically calculating an index with a fixed value based on selection criteria in DAX Commands and Tips

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

Newbie_1 — Wed, 23 Oct 2024 02:25:16 GMT

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

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

bhanu_gautam — Wed, 23 Oct 2024 04:27:19 GMT

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.