# 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

• 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

Monday, September 19, 2016 8:43 AM

### All replies

• Can you please post some sample data ?

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

Monday, September 19, 2016 8:43 AM