Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
User | Count |
---|---|
102 | |
92 | |
85 | |
78 | |
71 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |