Percentage based on maximum value of a row

[angsoka]

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

[angsoka]

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

[FreemanZ · Accepted solution]

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: