using max value measure as filter for other calculated measure

[tjapka]

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

1) I need to get unique weight value of 0.10, 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])) - here I find max for sum of Sales for every region;
• Weight_max_reg='market IQVIA'[Market]/(CALCULATE(sum('market IQVIA'[Sum TRD Price in EURO]);ALL(Region[Region]))) - here i calculate % of market allocation for every region;
• I need to get the constant weight value, based on regional maximum of sales value.

Region	Sales	Max_reg	Weight_max_reg	Desirable result
Spain	9 631,87	11072,74	0,15	0,10
Latvia	11072,74	11072,74	0,10	0,10

Pls help me to solve it, I have no idea...

[Greg_Deckler]

Should be something along the lines of:

Max_reg_weight = CALCULATE(MAX(Table[Weight_max_reg]),FILTER(Table,Table[Sales]=[Max_reg]))

A lot of assumptions made here. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[tjapka]

Dear Grag, thank you for your reply, but unfortunately weight max reg is calculated measure...

(Table[Weight_max_reg])

[Greg_Deckler]

Oh, then it's just:

Max_reg_weight = CALCULATE([Weight_max_reg],FILTER(Table,Table[Sales]=[Max_reg]))

See my comment above about a lot of assumptions, that was one of them. This is why you should read my post here: Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490...

[tjapka]

Dear Greg, thank you for your patience, bet this formula gives simply weight measure for every region, not the constant,depending on maximum of sales value (exactly sum of sales).

I put it like this and get real weight for each region...

=CALCULATE('market IQVIA'[Weight_Value];FILTER(raw;raw[Sec_Value]=raw[Potential_max_reg]))

Pls help me to cope this!

[Greg_Deckler]

Would love to help more, but need source data to replicate this properly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[tjapka]

Dear Greg! This is how Power Pivot table looks like. Sec Value is measure (simple sum of sale), Potential_max_reg is calculated as maximum value of sum of Sec Value, Weight is measure. Weght_max_reg - i need to calculate, where this measure should be calculated (filtered) from the maximum sales (11 073, Latvia - Sec Value) and show me unique weight for Latvia (0.10 - Weight_Value) in every region as constant value. Thank you in adv. If you need anything else, pls let me know.

[tjapka]

1) table name - raw

used in followin formulas:

Potential_max_reg:=CALCULATE(MAXX(ALL(Region[Region]);raw[Sec_Value])) - i use it for calculation, where Region - is for Region table (classifier);

Sec_Value:=CALCULATE(sum([SecEUR])) - formula shown in prior calculation (raw[Sec_Value])

2) table name - market IQVIA

used in followin formulas:

Market:=CALCULATE(SUM([Sum TRD Price in EURO])) - i use it for calculation below

Weight_Value:='market IQVIA'[Market]/(CALCULATE(sum('market IQVIA'[Sum TRD Price in EURO]);ALL(Region[Region])))

3) this is powerpivot table, where I need to get result of weight_max_reg, that is connected to maximum value and shows unique weight value (0.10) for all regions as constant.