Calculate percentage based on several catgeories

[PXJ]

Hi everyone,

I have tried several ways but I am unable to calculate percentage for the below:

I am trying to calculate percentage for NSW and VIC (see below table)

NSW Local = Local/(Local+Overseas)

NSW Overseas = Overseas/(Local+Overseas)

The above calculation should also be based on Year. Hope you guys can help. Thanks 

DM	KIC	Currency	Year	Amount
ABC	NSW	Local	2019	500
XYZ	VIC	Local	2019	600
XYZ	VIC	Local	2020	120
ABC	VIC	Overseas	2019	800
ABC	NSW	Local	2020	500
XYZ	VIC	Local	2021	100
ABC	VIC	Overseas	2019	900
ABC	VIC	Local	2020	100
XYZ	NSW	Overseas	2021	780
XYZ	VIC	Overseas	2019	980
ABC	NSW	Local	2020	123
ABC	NSW	Local	2021	120
XYZ	VIC	Overseas	2019	794
XYZ	VIC	Local	2020	852
XYZ	NSW	Local	2019	258
ABC	NSW	Overseas	2021	1470
XYZ	VIC	Local	2022	125
ABC	VIC	Overseas	2022	423
XYZ	VIC	Local	2022	324
ABC	VIC	Overseas	2022	861

[v-rzhou-msft · Accepted solution]

Hi @PXJ ,

I suggest you to create a measure and show it in a matrix.

Percentage =
VAR _AMOUNT =
    CALCULATE ( SUM ( 'Table'[Amount] ) )
VAR _TOTAL =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        ALLEXCEPT ( 'Table', 'Table'[Year], 'Table'[KIC] )
    )
RETURN
    DIVIDE ( _AMOUNT, _TOTAL )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.