locked
Count as percentage of self with detail RRS feed

  • Question

  • Hello. I created a measure that counts something. Then when I add another layer of detail in powerpivot it shows the correct information, but how do I get a % of Total when it's broken down at additional detail? For example, I have year first, then i add year by trade, how can I get a % of total for the year? 
    Wednesday, September 14, 2016 11:27 PM

Answers

  • You can establish all kinds of totals and subtotals by playing around with different ALL functions.

    In your case, ALL(Claims) removes all filters from the Claims table, resulting in [Claims with Cashouts] for all claims (if [Claims with Cashouts] itself filters the Claims table in some way, e.g. for removing claims without cashouts, that filter will be preserved as is it set after ALL(Claims) was applied).

    The total for a year can in many case be derived by not removing a filter on year. So if you have a [Year] column in Claims, you could use

    ALLEXCEPT(Claims, Claims[Year])

    to remove all filters on Claims except a filter on the year column. This would result in the total by year. Keep in mind, however, that the ALL functions only remove filters, not introduce new ones; so if your output does not filter on the [Year] column, you would end up with the overall total again. This happens in e.g. a pivot table where you have a column [MonthYear] in the row labels, or simply [Date].

    The best way to address this is to introduce a new filter that sets the year to whatever year(s) were present in the current context. This can be done with the VALUES function. So

    TotalClaimsByYear:= CALCULATE([Claims with Cashouts], ALLEXCEPT(Claims, Claims[Year]), VALUES(Claims[Year]))

    If you have [Year] in a separate table like Dates, it works quite the same:

    TotalClaimsByYear:= CALCULATE([Claims with Cashouts], ALLEXCEPT(Dates, Dates[Year]), VALUES(Dates[Year]))

    although you may want to remove other filters from the Claims table here as well.

    Friday, September 16, 2016 6:55 AM
    Answerer
  • Hi AlexMartini,

    The relationship between tables should be one(Date table) to many(Fact table) as the following screenshot.

    If you have any question, please don't hesitate to ask.

    Regards,
    Angelia

    Tuesday, September 27, 2016 2:51 AM

All replies

  • I tried this and it gives me the % of whole, but how do I do it for year? Thanks!!

    Claims with cashouts as a percentage of total cashouts:=[Claims with Cashouts]/CALCULATE([Claims with Cashouts],ALL(Claims))

    Also, what is the difference between doing above and this?

    [Claims with Cashouts]/All[Claims, [Claims with Cashouts]

    Wednesday, September 14, 2016 11:39 PM
  • Hello,

    Can you provide us some sample data and the expected result, please?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 15, 2016 6:05 AM
  • Hi Mr Olaf. It's pretty simple. In powerpivot, you can have years and then categories for each year. You can then say show values as % of parent total and then select year. I want to know how to do that in powerpivot. This is a picture of what I DON'T want as it shows each year as a % of the grand total. 
    Friday, September 16, 2016 2:11 AM
  • You can establish all kinds of totals and subtotals by playing around with different ALL functions.

    In your case, ALL(Claims) removes all filters from the Claims table, resulting in [Claims with Cashouts] for all claims (if [Claims with Cashouts] itself filters the Claims table in some way, e.g. for removing claims without cashouts, that filter will be preserved as is it set after ALL(Claims) was applied).

    The total for a year can in many case be derived by not removing a filter on year. So if you have a [Year] column in Claims, you could use

    ALLEXCEPT(Claims, Claims[Year])

    to remove all filters on Claims except a filter on the year column. This would result in the total by year. Keep in mind, however, that the ALL functions only remove filters, not introduce new ones; so if your output does not filter on the [Year] column, you would end up with the overall total again. This happens in e.g. a pivot table where you have a column [MonthYear] in the row labels, or simply [Date].

    The best way to address this is to introduce a new filter that sets the year to whatever year(s) were present in the current context. This can be done with the VALUES function. So

    TotalClaimsByYear:= CALCULATE([Claims with Cashouts], ALLEXCEPT(Claims, Claims[Year]), VALUES(Claims[Year]))

    If you have [Year] in a separate table like Dates, it works quite the same:

    TotalClaimsByYear:= CALCULATE([Claims with Cashouts], ALLEXCEPT(Dates, Dates[Year]), VALUES(Dates[Year]))

    although you may want to remove other filters from the Claims table here as well.

    Friday, September 16, 2016 6:55 AM
    Answerer
  • Thanks Michiel. Lots of good info here. I was able to get the % of total to work. 

    Cashout % of Total:=[Cashout Count]/CALCULATE([Cashout Count],ALL(Claims))

    However, I couldn't get the year %'s to work. This is what I tried. 

    Cashout % of Total Year:=[Cashout Count]/Calculate([Cashout Count],ALLEXCEPT('Date','Date'[Year]),VALUES('Date'[Year]))

    Sunday, September 18, 2016 10:53 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.  You can use the measure:

    percentage:=SUM(Table1[Amount])/CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Year])). 


    Then a pivot table is created as the right screenshot. It returns Country percentage of each year.

    If you have any questions, please feel free to ask.

     
    Regards,
    Angelia
    Monday, September 19, 2016 9:51 AM
  • Hi Angelia, 

    I tried your formula, but I'm still getting 100% for all my values. Why I am getting 100% for all the values? The only thing that I can think of is that the fact that date is a separate table (since that differs in your example). I'm going to try creating a calculated formula with year in the claims table and not connect to the date table. 

    Cashout % of Total Year:=CountA(Claims[Point of cashout])/CALCULATE(CountA(Claims[Point of cashout]),ALLEXCEPT('Date','Date'[Year]))

    Monday, September 19, 2016 10:32 AM
  • What does your model look like? You do have a relationship from the Claims to the Date table, right? And you do use Date[Year] in your ouput, not Claims[Year]? 
    Monday, September 19, 2016 3:23 PM
    Answerer
  • Hi Micheil. Yes, I do. It only works when I create a column reference to year in the claims table. If I try to reference the datetable it doesn't work. And they are connected. 
    Tuesday, September 20, 2016 5:59 PM
  • Can you post a picture of your model diagram? I still don't get what it looks like, or what you mean with a column reference here...
    Wednesday, September 21, 2016 7:38 AM
    Answerer
  • Hi AlexMartini,

    The relationship between tables should be one(Date table) to many(Fact table) as the following screenshot.

    If you have any question, please don't hesitate to ask.

    Regards,
    Angelia

    Tuesday, September 27, 2016 2:51 AM