none
Powerpivot - Sum of the Parts not Equaling Grand Total

    Question

  • I've created a measure counting instances of unique identifiers utilizing the following formula. However, the Grand Total does not equal the sum of the sub-totals. 

    =CALCULATE(COUNTROWS(DISTINCT('Rawdata File'[Identifier])),ALL('Rawdata File'[Identifier]))


    Wednesday, November 18, 2015 10:39 PM

Answers

  • So the following two statements describe many to many relationships

    b. A patient could have had a single plan name but visited 2 or more locations

    c. A patient could have had 2 or more plans and visited a single or multiple locations

    By definition when doing a distinct count over such a model then the grand total should not always be the sum of the rows above. If it was a simple sum then you would be double counting patients that had multiple plans or had visited multiple locations.

    So if you have 1 patient that visits locations A & B then your report should look like the following:

    Location A     1
    Location B     1
    TOTAL           1

    You should not have a total of 2. So I think your calculation is probably correct!


    http://darren.gosbell.com - please mark correct answers

    Friday, November 20, 2015 12:50 AM
    Moderator

All replies

  • It is possible that [Identifier] is not unique, and based on your measure, it seems you expect as much, since you're invoking DISTINCT() on the field.

    If multiple subtotal groupings contain the same [Identifier] then it will be counted separately in each subtotal grouping, but will only be counted once in the grand total, leading to your subtotals adding up to more than the grand total.

    Additionally, you are modifying you filter context to consider ALL('Rawdata File'[Identifier]). Context will be preserved, though on whatever subtotal groupings exist in the pivot table.

    Finally, ALL() returns a table of distinct values after removing context from the field(s) referenced within, so you should be able to replace this with the following measure:

    Measure:=
    COUNTROWS( ALL( 'Rawdata File'[Identifier] ) )

    To be able to provide further help, we'll need a better understanding of your model and reporting requirements. The best way to help us help you is to provide a workbook with a model defined and populated with sample data that highlights the issue at hand, along with a manually created mock-up of your expected output from that sample data.

    Failing that, a thorough description of your model and expected results will be necessary.

    GNet Group BI Consultant

    Thursday, November 19, 2015 1:04 AM
  • Hi Caspar,

    the important thing to remember here is that a Grand Total row does not show the sum of all rows above, but the result of your measure in the context of all rows (or: the context with all filters removed that come from the row labels above). As your measure is not a sum, the result could be anything depending on your data, as Greg mentioned.

    As a trivial example, when 'Rawdata File'[Identifier] is used for row labels in your pivot table, then the grand total row will show the same result as all row above...

    Thursday, November 19, 2015 8:47 AM
    Answerer
  • Greg - thanks for your response. Let me summarize the model by saying I have;

    1. A table that has a unique identifier created by concatenating [subscriberID]&[locationID]&[planID]&[providerID]

    2. Created a pivot utilizing a slicer to isolate [or consolidate] the data in the pivot table which had the following fields (reference tables were linked to add the Dept & Provider Names into the 'Rawdata File');

     
    DeptName LocID Provider Name ProvID Patient Cases

    3.  The 'Patient Cases' are a result of the formula listed earlier which will allow us to report out a couple of scenarios where;

    a. A patient could have had a single plan and visited a single location

    b. A patient could have had a single plan name but visited 2 or more locations

    c. A patient could have had 2 or more plans and visited a single or multiple locations

    Will this help understand how I've made this report out?

    Thanks

    Thursday, November 19, 2015 4:59 PM
  • Michiel - thanks for your response.  I agree as I had pulled all 276k+ rows of the unique identifier and put it into pivot to count the number of rows and they come out to my Grand Total.  If you look at what I responded to Greg above, maybe its how I'm reporting out the data that is causing the sub-total counts to be incorrect?  I am using a report someone else made as the reference point for comparison purposes but believe that his report, which reports out tabular information (with related formulas that 'look at the row above') through a pivot table, could be so much more streamlined through PwrPvt.  Unfortunately, both our subtotals match precisely but his Grand Total reflects those subtotals in aggregate and mine does not.
    Thursday, November 19, 2015 5:05 PM
  • Normally, Power Pivot does not return incorrect results - it may not be what you want, though... Your subtotals and grand total will return the result of your measure for the context implied by the subtotal or grand total rows.

    As Greg mentioned already, the DISTINCT in your measure means that the grand total would not equal the sum of the subtotals, as some Identifiers will be counted in multiple subtotals, but only once in the grand total. If the other report returns the sum of the subtotals as the grand total, it means that it applies another calculation to the grand total row than to the other rows. If you really want that, you can do it in Power Pivot as well with something like

    IF(ISFILTERED([your row label column here]),[Measure for details and subtotals],[Measure for grand total])

    (You could run into performance issues with this formula for very large pivot tables, but that is another topic.)

    Thursday, November 19, 2015 8:43 PM
    Answerer
  • I still have no idea what your pivot table looks like, or what your model looks like.

    That being said, if you want to add up the subtotal values, that's easy enough to do.

    DistinctIdentifier:=
    COUNTROWS( ALL( 'Rawdata File'[Identifier] ) )
    
    SumDistinct:=
    SUMX(
        VALUES( 'Rawdata File'[<whatever subtotal field>] )
        ,[DistinctIdentifier]
    )

    SUMX() steps row by row through the table that you pass to it as the first argument. In this case we are using VALUES(), which will output a single column table with the distinct values of a given field. Whatever field is the subtotal level you're using is what you should put there.
    SUMX() then evaluates the expression in its second argument for each row in that table.

    At the subtotal level, the behavior is effectively identical to your original solution - we're iterating over a 1-row table. At the grand total level, though, it will step through each of the subtotal values, and sum the count at each level into a single grand total.

    GNet Group BI Consultant

    Thursday, November 19, 2015 9:01 PM
  • So the following two statements describe many to many relationships

    b. A patient could have had a single plan name but visited 2 or more locations

    c. A patient could have had 2 or more plans and visited a single or multiple locations

    By definition when doing a distinct count over such a model then the grand total should not always be the sum of the rows above. If it was a simple sum then you would be double counting patients that had multiple plans or had visited multiple locations.

    So if you have 1 patient that visits locations A & B then your report should look like the following:

    Location A     1
    Location B     1
    TOTAL           1

    You should not have a total of 2. So I think your calculation is probably correct!


    http://darren.gosbell.com - please mark correct answers

    Friday, November 20, 2015 12:50 AM
    Moderator
  • All - sorry for the delay in responding (I was out on business then vacation).

    Your responses give me some ideas to delve deeper into and, to Darren's point, it's not a matter of simple summing but counting the uniqueness of the identifier (patient_id&location_id&plan_name&provider_id).

    I'll look further into your suggestions which will help me become more literate in some of the functions powerpivot has to offer. I have already found it a useful tool.

    Thursday, December 3, 2015 4:07 PM