locked
How to calculate % of each year when there are breakdowns on the rows RRS feed

  • 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



    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



    Monday, September 19, 2016 8:43 AM