using max value of measure to filter other connected value

[tjapka]

Good day, good people! Pls help me to solve not so difficult, but tricky case:

1) I need to get weight value of 0.10 for all cells (Desirable result), that corresponds to the maximum value of 11 072.74 as filter (for region Latvia). In other words, I need to recognize, that maximum value of sales is for Latvia (11 072,74) and relate weight value for that region - 0,10 - for all other countries (desirable result).

Max_reg=CALCULATE(MAXX(ALL(Region[Region]);raw[Sec_Value]))
Weight_max_reg='market IQVIA'[Market]/(CALCULATE(sum('market IQVIA'[Sum TRD Price in EURO]);ALL(Region[Region])))


Region    Sales             Max_reg       Weight_max_reg Desirable result
Spain     9 631,87          11 072,74      0,15                            0,1
Latvia    11 072,74         11 072,74      0,1                              0,1

e.t.c.

[technolog · Accepted solution]

To accomplish this task, we can leverage the approach of comparing a region's sales to the maximum sales value (max sales value of all regions). If a particular region's sales are equal to the max value, then retrieve the weight for that region and apply it across the board.

Here's how you can achieve this:

Determine which region has the max sales.
Get the weight for that region.
Apply this weight across all other regions.

Let's break this down into steps in DAX:

Calculate the region with the maximum sales:
Max_Sales_Region =
CALCULATE(
VALUES(Region[Region]),
FILTER(
ALL(Region[Region]),
'market IQVIA'[Sum TRD Price in EURO] = [Max_reg]
)
)
This will give you the region with the max sales.

Now, calculate the weight for that region:
Weight_For_Max_Sales_Region =
CALCULATE(
SUM('market IQVIA'[Market]) / CALCULATE(SUM('market IQVIA'[Sum TRD Price in EURO]), ALL(Region[Region])),
FILTER(
ALL(Region[Region]),
Region[Region] = [Max_Sales_Region]
)
)
Use the above measure to create your 'Desirable result' column:
Desirable_Result = [Weight_For_Max_Sales_Region]
This will give you the weight for the region with the maximum sales (in your case, Latvia) and apply this weight across all regions in the 'Desirable result' column.