Forum Discussion
Calculate Percentages In A Hierarchical Form In Power BI
I have a client/country sales matrix in power bi. How can i calculate de column 2019%
2019 2019 % 2020 ....
client 1 30 33%=30/90
Portugal 10 33% =10/30
França 20 66% = 20/30
Client 2 60 66,6%= 60/90
Espanha 30 50% = 30/60
Bélgica 30 50% = 30/60
total 90 100%
HI Anonymous
This measure should do the trick. I am assuming your data is coming from one table (if yes, please condider re-wiring into a dimensional model).Percentage = VAR countryInScope = NOT ISINSCOPE ( 'Table'[Country] ) VAR result = DIVIDE ( SUM ( 'Table'[Amount] ), IF ( countryInScope, CALCULATE ( SUM ( 'Table'[Amount] ), REMOVEFILTERS () ), CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Client] ) ) ) ) RETURN result
You are welcome to create a separate measure for Sales Sales = SUM('Table'[Amount]) and use it in the measure above instead of repeating the SUM func.
If I answered your question, please mark my post as a solution. Thank you!
1 Reply
- nickchobotarSkilled Sharer
HI Anonymous
This measure should do the trick. I am assuming your data is coming from one table (if yes, please condider re-wiring into a dimensional model).Percentage = VAR countryInScope = NOT ISINSCOPE ( 'Table'[Country] ) VAR result = DIVIDE ( SUM ( 'Table'[Amount] ), IF ( countryInScope, CALCULATE ( SUM ( 'Table'[Amount] ), REMOVEFILTERS () ), CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Client] ) ) ) ) RETURN result
You are welcome to create a separate measure for Sales Sales = SUM('Table'[Amount]) and use it in the measure above instead of repeating the SUM func.
If I answered your question, please mark my post as a solution. Thank you!