Answered by:
How to calculate % of each year when there are breakdowns on the rows
Question

Hello. I have row labels and column labels that are years. I want to calculate the % that each row label is of each year. How can I do that? I tried the formula below but it just returned all 100%'s for every category for every year.
Cashout % of Total Year:=[Cashout Count]/CALCULATE([Cashout Count],ALLEXCEPT('Date','Date'[Date]))
Sunday, September 18, 2016 11:13 AM
Answers

Hi AlexMartini,
ALLEXCEPT Function removes all context filters in the table except filters that have been applied to the specified columns('Date'[Date] in your formula).
I am trying to reproduce your scenario in my sample data and get the expected results. Country is used as category for every year in the following left screenshot. I create a measure:
percentage:=SUM(Table1[Amount])/CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Year]))
Then a pivot table is created as the above right screenshot. It returns Country percentage of each year.
If I use ALL function in filter, and create another measure:percentage2:=SUM(Table1[Amount])/CALCULATE(SUM(Table1[Amount]),ALL(Table1))
I will get the different results. The below screenshot show the percentage of country for total amount. For example, 0.1168 equals 9/77, 0.2597 equals 20/77.
If this is not what you what, please give us more details and sample data structure.Regards,
Angelia
 Edited by Angelia ZhangMicrosoft contingent staff Monday, September 19, 2016 9:11 AM
 Proposed as answer by Angelia ZhangMicrosoft contingent staff Monday, September 26, 2016 10:39 AM
 Marked as answer by Charlie Liao Thursday, September 29, 2016 9:03 AM
Monday, September 19, 2016 8:43 AM
All replies

Can you please post some sample data ?
If this answer is helpful to you .. Please mark as Answer....
Sunday, September 18, 2016 12:26 PM 
Hi AlexMartini,
ALLEXCEPT Function removes all context filters in the table except filters that have been applied to the specified columns('Date'[Date] in your formula).
I am trying to reproduce your scenario in my sample data and get the expected results. Country is used as category for every year in the following left screenshot. I create a measure:
percentage:=SUM(Table1[Amount])/CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Year]))
Then a pivot table is created as the above right screenshot. It returns Country percentage of each year.
If I use ALL function in filter, and create another measure:percentage2:=SUM(Table1[Amount])/CALCULATE(SUM(Table1[Amount]),ALL(Table1))
I will get the different results. The below screenshot show the percentage of country for total amount. For example, 0.1168 equals 9/77, 0.2597 equals 20/77.
If this is not what you what, please give us more details and sample data structure.Regards,
Angelia
 Edited by Angelia ZhangMicrosoft contingent staff Monday, September 19, 2016 9:11 AM
 Proposed as answer by Angelia ZhangMicrosoft contingent staff Monday, September 26, 2016 10:39 AM
 Marked as answer by Charlie Liao Thursday, September 29, 2016 9:03 AM
Monday, September 19, 2016 8:43 AM