topic Percentage based on maximum value of a row in DAX Commands and Tips

Percentage based on maximum value of a row

angsoka — Thu, 04 May 2023 01:24:08 GMT

Dear all,

I want to create a measure on powerpivot which calculate the percentage over the maximum value and specific country (for example Thailand). Below is the part of the data.

Year	Country	Export
2021	Indonesia	60
2021	Vietnam	70
2021	Singapore	80
2021	Thailand	100
2022	Indonesia	200
2022	Vietnam	120
2022	Singapore	150
2022	Thailand	100

I want to create two measurements %Max and %Thailand

Year	Country	Export	%Max	%Thailand
2021	Indonesia	60	60%	60%
2021	Vietnam	70	70%	70%
2021	Singapore	80	80%	80%
2021	Thailand	100	100%	100%
2022	Indonesia	200	100%	200%
2022	Vietnam	120	60%	120%
2022	Singapore	150	75%	150%
2022	Thailand	100	50%	100%

Any help is appreciated!

angsoka

Re: Percentage based on maximum value of a row

FreemanZ — Thu, 04 May 2023 02:03:17 GMT

hi angsoka

try to plot a table visual with necessary columns and two measures like:

%Max = DIVIDE( MAX(data[Export]), MAXX( FILTER( ALL(data), data[Year]=MAX(data[Year]) ), data[Export] ) )

%Thailand = DIVIDE( MAX(data[Export]), MAXX( FILTER( ALL(data), data[Year]=MAX(data[Year]) &&data[Country]="Thailand" ), data[Export] ) )

it worked like:

Re: Percentage based on maximum value of a row

angsoka — Fri, 05 May 2023 03:57:03 GMT

Dear FreemanZ

Thank you so much. It works like a charm.
However, when I use that same measure for this table (I add one column called "Goods"). It doesn't work. Is there any filter that I should add?

Year	Country	Goods	Export
2021	Indonesia	A	60
2021	Vietnam	A	70
2021	Singapore	A	80
2021	Thailand	A	100
2022	Indonesia	A	200
2022	Vietnam	A	120
2022	Singapore	A	150
2022	Thailand	A	100
2021	Indonesia	B	70
2021	Vietnam	B	60
2021	Singapore	B	90
2021	Thailand	B	100
2022	Indonesia	B	200
2022	Vietnam	B	130
2022	Singapore	B	140
2022	Thailand	B	80