locked
PowerPivot Percent of Total RRS feed

  • Question

  • I've thrown the towel... And I am praying someone can pick my pieces up.

    In PivotTable, we had a nice measure that would give us the "Percent of Total". Just to make life easier, we can use AdventureWorks for this example. Here is the deal:

    1 - Values --> Sum of FactInternetSales (OK)

    2 - RowLabels --> CalendarYear (OK)

    I am able to calculate PCT OF TOTAL (this is the name I gave to my measure) like so:

    =SUM(FactInternetSales[SalesAmount])/
                   CALCULATE(
                                         SUM(FactInternetSales[SalesAmount]),
                                         ALL(DimDate[CalendarYear])
                                         )

    It works fine, until I filter the years, since it continue to consider the entire range of data due to the ALL function. I tried to adapt it so that the CALCULATE portion reads:

    CALCULATE(
                      SUM(FactInternetSales[SalesAmount]),
                      ALLEXCEPT(DIMDATE,DimDate[CalendarYear])
                          )

    However, instead of giving me the sum of ALL the items (like it does in the first case with ALL) EXCEPT for those selected, it slices the summation by CalendarYear I selected and, thus, I get 100% on each row rather the appropriate percentage.

    I tried using DATESBETWEEN, but ended up like ALLEXCEPT. I tried DATESINPERIOD to offset the number of days I wanted to add, but to no avail. Everything I have tried will aggregate by year, instead of by the filtered years.

    The button line is: I need to find the grand total of the filtered data... Sigh of desperation... :-)


    Robert Martim - MVP Excel http://loja.msofficegurus.com www.msofficegurus.com www.wordpower.com.br
    Sunday, March 6, 2011 6:04 PM

Answers

  • Try again using Denali PowerPivot and the Allselected function:

    =SUM(FactInternetSales[SalesAmount])/
    CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    ALLSELECTED())

     

    This should give you the right answer

    Sunday, July 17, 2011 9:54 PM
  • Hi Josh

    Actually you request is easier to accomplish, due to the fact that - unlike the first post above - we do not have conflicting filters that force us to create a new column in order to solve the context problem.

    In the case you post, we can create a regular DAX measure that will automatically refine the context based on the 'reportFilter' field:

    SUM(Table1[thisYear]) / CALCULATE(SUM(Table1[thisYear]), ALLEXCEPT(Table1,Table1[reportFilter]))

    Which yields the following output (expression is only for 'this year', you can use the same for the 'last year' and 'diff'):


    The context in this case properly responds to the pivot table filter (Table1[reportFilter]) , as the ALL function in the expression is excluding it when clearing out all filters in the denominator.

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx

    Sunday, March 20, 2011 11:12 AM
    Answerer

All replies

  • Have you considered using a second time dimension?

    Use one in your filter and the second in the ALL part.

    Sunday, March 6, 2011 9:40 PM
  • Have you considered using a second time dimension?

    Use one in your filter and the second in the ALL part.

    I have tried as follows:

    =CALCULATE(
                      SUM(FactInternetSales[SalesAmount]),
                      DATESBETWEEN(
                                                     DimDate[FullDateAlternateKey],
                                                     FIRSTDATE(ALL(DimDate[FullDateAlternateKey])),
                                                     LASTDATE(DimDate[FullDateAlternateKey]))
                          )

    But, again, I don't get the TOTAL SUM for the filtered data. All I keep getting is the SUBTOTAL for the filtered data. Basically, I have been unable to replicate the "% of total" option in a calculated field in a PivotTable. I either get a percent of the TOTAL (whether filtered or not) or 100% of the filtered data. In both cases, the results do not meet my needs.


    Robert Martim - MVP Excel http://loja.msofficegurus.com www.msofficegurus.com www.wordpower.com.br
    Sunday, March 6, 2011 10:27 PM
  • I meant creating an addtional table in your model.

    See Kasper de Jonge 's blogpost: http://www.powerpivotblog.nl/powerpivot-time-intelligent-functions-revisited-why-use-all-and-how-to-work-around-it

    Marco Schreuder http://blog.in2bi.com

     

     

    Monday, March 7, 2011 7:23 AM
  • I had seen that post from Kasper, but I am just baffled that I am unable to create a fórmula that will return what I need without resorting to artifices. My knowledge of DAX is limited.

    I will look at the sendo link though.


    Robert Martim - MVP Excel http://loja.msofficegurus.com www.msofficegurus.com www.wordpower.com.br
    Monday, March 7, 2011 6:16 PM
  • Hello Robert,

    To my knowledge this is not possible in the current version of PowerPivot. Row and filter contect make it impossible to gain access to the values you want.

    Kasper

    Wednesday, March 9, 2011 4:59 PM
  • Hi,

    I actually found a way to do it...  What we need is a way to include the Query context filters in the DAX calculation.  Normally, you can only affect Row and Filter context, but with the technique below you can detect the Query filter within the DAX formula

    I am assuming you had a setup in which the slicer as well as the 'Row Labels' of the pivot table were populated from DimDate[CalendarYear].  After making a selection on the slicer it will restrict what Years in the pivot table row but also affected the context of the DAX calculation.

    Using the same Adventure Works data model, create a new calculated column in the FactResellerSales table as:

    RELATED(DimDate[CalendarYear])

    Create a new pivot table, and keep the regular DimDate[CalendarYear] in the slicer, but replace the 'Row Labels' on the pivot table for this new calculated column (called for example 'Fact Year').  Then use this DAX calculation for percent of total:

    SUM(FactInternetSales[SalesAmount]) /
     CALCULATE(
      SUM(FactInternetSales[SalesAmount]), ALL(FactInternetSales[Fact Year])
      )

    This time, when you change the selection of Years in the slicer, the calculation will execute only using the total of elements in the Query context, instead of all available years.  If you select 1, 2 or all, the sum of all rows will always add up to 100%

    The reason why this works is that the slicer propagates the context to inlcude only rows in the FactInternetSales that belong to the selected years, and applying the ALL function now takes into account those years instead of all years in the DimDate table. You couldn't use the DimDate[CalendarYear] in the 'Row Labels' of the pivot table as that will change the calculation context to use only the Year in the current row.

    Hope that helps!




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    • Proposed as answer by Wreckem_OKC Wednesday, May 30, 2012 7:47 PM
    • Unproposed as answer by Wreckem_OKC Wednesday, May 30, 2012 7:49 PM
    Wednesday, March 9, 2011 6:46 PM
    Answerer
  • Robert,

    You wrote "In PivotTable, we had .."

    You probably know that this but anyway: the (old)pivottable functionality is still there .. Right Click in the data area of you pivot table and select "Value field settings" and select % of ..

     

    Marco Schreuder http://blog.in2bi.com

     

    Wednesday, March 9, 2011 7:18 PM
  • Javier, I might be doing it wrong, but your solution does not seem to work when you only have one fact table. How would you suggest writing-up a feature request for getting the column within context of the query? I would be great if I could get something like =sum(QueryContext(myFactTable[dollarSales])).

     

    • Edited by JoshRoss Friday, March 18, 2011 9:18 PM added picture
    Friday, March 18, 2011 8:41 PM
  • Hi Josh

    Actually you request is easier to accomplish, due to the fact that - unlike the first post above - we do not have conflicting filters that force us to create a new column in order to solve the context problem.

    In the case you post, we can create a regular DAX measure that will automatically refine the context based on the 'reportFilter' field:

    SUM(Table1[thisYear]) / CALCULATE(SUM(Table1[thisYear]), ALLEXCEPT(Table1,Table1[reportFilter]))

    Which yields the following output (expression is only for 'this year', you can use the same for the 'last year' and 'diff'):


    The context in this case properly responds to the pivot table filter (Table1[reportFilter]) , as the ALL function in the expression is excluding it when clearing out all filters in the denominator.

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx

    Sunday, March 20, 2011 11:12 AM
    Answerer
  • Marco

    Yes, I know the option is there, I just wanted to be able to calculate the damn thing... I disappeared from the post due to work commitments, but I will try the suggested calculation here.

    Thanks


    Robert Martim - MVP Excel http://loja.msofficegurus.com www.msofficegurus.com www.wordpower.com.br
    Thursday, May 5, 2011 11:41 PM
  • Try again using Denali PowerPivot and the Allselected function:

    =SUM(FactInternetSales[SalesAmount])/
    CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    ALLSELECTED())

     

    This should give you the right answer

    Sunday, July 17, 2011 9:54 PM