Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |