Busy week here at the MVP Summit in Redmond.  As usual, I can’t tell you anything I have learned this week – it’s strictly under NDA.  But I have seen some really exciting things – at one point today I screamed out loud “YES!” in a packed room, and a little while later I said “I don’t think we can be stopped if we had something like this.”  Where the “we” means us – me and you.

Enough of that.  Here’s one from my archived list of “topics to cover on the blog at some point.”

Sales per Day Measure

Check out this relatively simple pivot:

image

Note that the subtotals for [Sales per Day] do not equal the sum of their parts.  2002’s total is $17,891 but if I add Mountain Bikes plus Road Bikes myself, I get a number closer to $19,000:

PowerPivot totals don't add up how do I fix it?

Totals don’t add up!

The formula for [Sales per Day] is:

[Sales per Day] =
[Sales] / COUNTROWS(DISTINCT(Sales[OrderDate]))

Which, in English, equates to “My Sales measure divided by the number of days for which I have sales transactions.”

Why Doesn’t it Add Up?

Well, if the “number of days for which I have sales transactions” is different for Mountain Bikes vs. Road Bikes, that will cause this problem.  I can illustrate by adding that as a separate measure:

[Days I have Transactions] =
COUNTROWS(DISTINCT(Sales[OrderDate]))

image

The Reason Why Sales per Day Doesn’t “Add Up”

So I only sold Mountain Bikes on 96 days in 2001, and Road Bikes on 180 days, but on 181 days, I sold at least one bike of any sort.  This is why it doesn’t add up.

“Shouldn’t you divide by Calendar[Date] instead of Sales[OrderDate]?”

Some of you will no doubt have this question already:  Isn’t it more accurate for [Sales per Day] to be based on the number of days in the calendar rather than the number of days on which it sold?”

And my answer is “probably, but it depends, AND this is a good example of a problem you WILL hit sooner or later in completely legitimate cases.”

For instance, if I opened my store in July 2001 and didn’t offer Mountain Bikes at all until September, I don’t want to divide either of my Sales amounts by 365, and I certainly don’t want to divide Mountain Bike sales by 180.

Even then though, I should probably have a separate table like Inventory or maybe [Start Date] and [End Date] columns in my Products table, and use those to create a measure named [Days Offered] measure, and use THAT as my denominator instead.  That would be the fairest/most accurate approach. 

But it would STILL have this same “doesn’t add up” problem.  So let’s move on to a fix.

Forcing the Totals to Add Up

Let’s write a new measure:

[Sales per Day FIXED] =
IF(COUNTROWS(VALUES(Category[Name]))=1,
   [Sales per Day],
   SUMX(VALUES(Category[Name]), [Sales per Day])
)

In English, this says

“If I am in a pivot cell that corresponds to a single product category like Mountain Bikes, then just use the normal [Sales per Day] measure.  But if the pivot cell corresponds to more than 1 category, that means I am on a total, and then I want to have the total be the sum of all of the individual categories beneath it.”

For details on the whole “IF COUNTROWS” thing, see this post.

For details on SUMX, see this post.

Does it work?  You bet:

PowerPivot totals fixed to add up

The Second Measure Works

Pretty slick.  There are shortcomings to this of course.  If I put something other than Category on the pivot, the measure won’t work right.  It is “tied” to the Category field.  So it isn’t quite as portable as most measures, but it still is amazingly useful when you need it.

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 19 Comments

  1. First, thanks for this post it proved very helpful!

    Perhaps you have already gone beyond this and solved the shortcoming, but if not, hopefully this will prove helpful.

    Regarding the shortcoming you specify at the bottom,

    “There are shortcomings to this of course. If I put something other than Category on the pivot, the measure won’t work right. It is “tied” to the Category field. ”

    I wasn’t sure if you were using “Category” as a general term in your example, or referring specifically to the Category of Bikes (ex. Mountain Bike). If it is specific to the Category of Bikes, then what you say would hold true if you wanted to switch out the Category for, say Bike Color, since the formula is tied to Category.

    However, if you were to tie the formula to the lowest level (unique record) of your Bikes table (not sure what tables you have), you can then drag in any classifying information (color, category, wheelsize, etc.) and it will properly summarize the subtotal and grandtotals.

    [Sales per Day FIXED] =
    IF(COUNTROWS(VALUES(Category[Name]))=1,
    [Sales per Day],
    SUMX(VALUES(BIKES[BikeID]), [Sales per Day])
    )

    To go a little further, if you also wanted to break apart your [Sales per Day] by both Classifying info on the rows and by month across the columns, you could use the formula below to ensure sub and grandtotals are correct on all levels by tying the formula to both the lowest level of the bikes and calendar tables.

    IF(AND(COUNTROWS(VALUES(Calendar[Day]))=1,COUNTROWS(VALUES(Bikes[BikeID]))=1),
    [Sales per Day],
    IF(COUNTROWS(VALUES(Calendar[Day]))=1,
    SUMX(VALUES(Bikes[BikeID]),[Sales per Day]),
    IF(COUNTROWS(VALUES(Bikes[BikeID]))=1,
    SUMX(VALUES(Calendar[Day]),[Sales per Day]),
    SUMX(VALUES(Calendar[Day]),SUMX(VALUES(Bikes[BikeID]),[Sales per Day]))
    )
    )
    )

  2. Hi everybody,

    I’m a little bit puzzled regarding the logic of Powerpivot in terms of the comparison of the sum of all rows for a calculated field (measure) and the gradn total.

    When I sum up all rows I do not get the value of the grand total. I gues this is connected with the fact, that my measure is used by the grand total cell, right?

    Bute, what I need is the sum of the rows and not the grand total which is using the logic of the measure.

    Do you have a hint for me??

    I appreciate your response, thank you very much!
    Gökhan

  3. Gökhan, In PowerPivot, each cell in the table is calculated individually, including total rows. Totals are *not* a sum of the detail rows like in a traditional PivotTable. The formulas are calculated –every single cell. The total you want is like a weighted average, and PowerPivot gives you a normal average. The formulas in the post above take care of this by using SUMX, which builds the total row by row (like a weighted average). For large tables, SUMX is expensive. A simple SUMX(Category[Name], [Sales per Day]) would work for the example above, but it would take a lot longer to run if the tables are large. You can mitigate the impact of SUMX by limiting it to a small table via VALUES(column) and only doing SUMX on the total rows via IF(COUNTROWS=1).

  4. Hello,

    Excellent post–very useful!

    I am however having an issue when I am using multiple “rows”..

    For example, I have in my rows area of the pivot, Year, Month, Product Category and Model.

    When I use the formula below I get the correct sum for the category level, but the Month and Year do not sum correctly…Any thoughts?

    Formula:

    if(HASONEVALUE(Products[model]),
    [SalesDifference],
    sumx(values(Products[model]),[SalesDifference])
    )

    Any suggestions are greatly appreciated!

    Thanks,
    Josh

  5. I made a calculated field that ignores a pivot filer related to a product
    “=CALCULATE(SUM([Number of Incidents]), ALL(Q4toQ1[Major Incident]))” when I bring in the date fields into the pivot the numbers all line up perfectly but the subtotal is off since it is looking at all dates and not the dates that are pivoted on the filter “Major Incident”. How do I get the best of both worlds and get the subtotal to add only the the dates that are in the pivot?

    1. The resolution was =CALCULATE([Sum Incidents], ALL(Q4toQ1[Major Incident]), DISTINCT(Q4toQ1[Reported Date])) adding the “distinct” portion as the last filter argument provided the filtered down details I needed!!!

  6. Hi,
    What if fact table has a different time stamps and for day granularity I need a summary by date A and for the month granularity at the same table I have to use date B?

  7. Hello, I tried this formula for my problem, but it didnt work

    I need to have a sum of the averages but a little bit different

    I have selling items (column [items] ) and selling dates (column [sellingdates] ) with selling persons (column called [employees] ).

    I have Items A, B, C, D (column in base[Items] )
    and they are seeling on different dates during the months (usual 1 or 2 sells during the month)

    Row Labels Correct result would be Average Sell value USD

    John – September 2015 154 77
    Item A 78 78
    1.9.2015 78 78
    30.9.2015 78 78
    Item B 76 76
    1.9.2015 76 76
    30.9.2015 76 76

    Rick – September 2015 150 75
    Item C 90 90
    3.9.2015 90 90
    30.9.2015 90 60
    Item D 60 60
    3.9.2015 60 60
    30.9.2015 60 60

    So what I am trying to get is the sum of items averages (regardless of the number of selling dates).
    So for me the average for John on Item A during september is 78 and the average fot the item B is 76, and I want to sum those two average, and not (78+78+76+76)/4=77

    All the items have their full names and codes in two different columns, but I want to ignore them all.

    Can you please help me, because I tried several solutions and neither worked.

  8. please help. have no idea how to show distribution for brand, because distribution for brand shouldn’t be calculated from distribution for the SKUs in this brand. please help.

  9. I was having a “Grand Total” issue where I Sum measure for ‘Actuals’ and ‘Forecasts’ depending on the member. A Member ‘X-Y Forecast’ means X months sum of Actual and Y months sum of Forecast. Another member ‘A-B Forecast means A months sum of Actual and B months sum of Forecast. in Grand total section for Rows and Columns I was only getting sum of Forecast numbers but no Actuals. I kind of understand why Grand total is wrong. I create a Boolean to get for how many months I need to add Actuals and how many months I need to add Forecasts. In Grand Total Area I think the same logic appears and it sums Forecasts only.

    Forcing with Sumx and Values as described above, get the Row cells ‘Grand total’ to be fixed, but Column cells ‘Grand Totals’ are still off. Is there a way to force ‘Grand Total’ for column cells too?

    1. Stupid me!! I just need to use the other Dimension Member to sum up!!…
      *** This post was really helpful to solve my problem.

      Thank you

  10. I am having a similar problem but in the different direction. I’m using the following formula to connect the budgets associated in the ‘Regions’ table with the right job in the ‘RAW’ table:
    Job_Budget:=CALCULATE(sum(Budgets[Budget]),raw,Regions,Regional_Budget)

    The tables are connected by job number. The issue comes in when I try to add in employees from the RAW table into the Pivot Table. It then lists the budget for the job across the row for all of the employees as well. I want it to be blank once the user drills down to that level so that the totals add up (note that the totals add up regardless but it then repeats them across every employee line).

    I know there is probably a simple solution out there but I can’t find it to save my life! Any guidance on what I can do to keep the rows blank once they drill into the employee level?

    Thanks!!

  11. Okay I’ve been trying to figure something out for two days and I’m about to give up on DAX. If you take a simple table with dates and qty. The link a date table. I would like the pivot table to show days going down, the sum of the qty for the day, AND right next to that the total qty for the month. I can NOT get this to work. I can get the total for the whole table, I can get mtd, ytd, and the rest. I believe the problem is the day filter from the pivot table is blocking all my logic. If I use ALL then how is measure supposed to know what month to return?
    The pivot should look like this:
    Rowlabel sum of qty MonthSum
    2/1/2016 4 19
    2/2/2016 6 19
    2/3/2016 1 19
    2/4/2061 8 19

    The reason for this is to do all sorts of calculations with MonthSum. Then graph them by day.
    Any help would be great.
    Thanks
    John

  12. Hi Rob, how to you exclude the totals at the bottom of pivot with a measure?

    For example I have tried this syntax: =IF(HASONEVALUE(‘TL Report_Source Data'[Grouped Company Name]),DISTINCTCOUNT(‘TL Report_Source Data'[Grouped Company Name]),BLANK()) – the result is 1 per filter context, in my case, a county name. This is correct as where a value exists [Grouped Company Name], this expression returns the value of 1 (TRUE) and the total at the bottom of the pivot shows no value (so far so good). BUT, how do I correctly calculate the total DISTINCTCOUNT value for the [Grouped Company Name] so I see the value greater than 1?

    Thank you, Alan

  13. I was told to add 10 percent to the cost and quote. Instance 1. I added 10% to the subtotals individually. Took the Grand Total. Instance 2. I added 10% percent straight away to the Grand Total. Both of them doesn’t tally. Kindly help.

  14. Hi Rob you explained very well but i have a problem that i want to show the total in a row. how can we do that
    ex: sales biscuits : 12
    sales Chiclets : 10
    sales Soaps : 15
    Sales washing : 10

    Totals sales : 47

    means all belongs to sales department i want show sum of them in a row, how can i do. please guide me its bit urgent.
    many thanks

    1. You want the total to appear in a “regular’ row rather than in a total row?

      This post may give you some ideas: http://powerpivotpro.com/2014/01/grand-total-mania-totals-at-top-multiple-totals/

      But basically, you will need to add something like “Total” as a real row in your source data, just like Biscuits and Soaps in the [ProductsColumn], and then “detect” when you’re on the “Total” row using IF, and use SUMX(ALL(ProductsColumn), [Sales Measure]) when you find that you’re in the “Total” row.

Leave a Comment or Question