locked
SumX All Total Issue RRS feed

  • Question

  • Hello,

    I am trying to create an index where I have a specific month offset (month -1) calculate only where I have values from a simple sum function.

    My model is simple I have a fact table with an ID, an Offset column, a UsageCol column and an unused date column.

    This is related to an offset column with values of -1 to 10 or so.  I am attempting to take my value for offset value = -1 and divide that by each subsequent number in the offset.  This appears to work at the individual ID level with the following functions:

    Usage:=SUM('Fact'[UsageCol])

    Measure 1:=sumx(FILTER(ALL(Index[Index]),Index[Index]=-1&&countrows(VALUES('Fact'[UsageCol]))),[Usage])

    Column Labels
    Row Labels -1 0 1 2 3 4 5 6 7 Grand Total
    Usage
    1 100 110 120 130 140 150 750
    2 50 40 40 40 40 40 40 290
    3 5 5 10 15 35
    4 10 10 10 30
    5 100 101 102 103 104 105 106 107 108 936
    Measure 1
    1 100 100 100 100 100 100 100
    2 50 50 50 50 50 50 50 50
    3 5 5 5 5 5
    4 10 10 10 10
    5 100 100 100 100 100 100 100 100 100 100
    Total Usage 265 266 282 288 284 295 146 107 108 2041
    Total Measure 1 265 265 265 265 265 265 265 265 265 265

    Here is the total that I am after to do my math with against [Usage].

    Total Measure 1 265 265 265 255 250 250 150 100 100

    My Measure 1 is working except for at the total level.  I want to add the numbers that are there so I can get the proper number to index against.

    https://drive.google.com/file/d/0B9j5ve8z-DgPZ1NyYTk2ZmRObFk/view?usp=sharing

    Here is the file.  Hope someone can help me solve this one.

    Tuesday, June 7, 2016 2:33 PM

Answers

  • With Power Pivot, the cells calculate individually which is a little counter intuitive when viewing a pivot table because it makes you think that Totals are really the totals of the numbers displaying above them.  Sometimes that's true, but when the filter logic becomes more complex or you begin incorporating ratios into your calculations, that no longer applies.

    Basically that means that each cell in the total rows is calculated on its own regardless of the rows above it.  So the filters from the pivot table being passed to your measure are all ID's (rows) and all Indexes (columns).

    To force the pivot to show the "correct" total based on individual rows, you must iterate your measure over those rows and add up the final result.  This is what SUMX() will do.

    So to get the result you are looking for with your existing measures and current pivot table layout, you want something like this:

    CorrectTotals=
    SUMX(
        VALUES('Fact'[ID]), 
        [measure 1]
    )

    This is saying, "Calculate the result of Measure 1 for each unique value of ID, then at the end, sum the results of those calculations."

    That will work but you should probably consider taking it a step further to simplify your Measure 1.  SUMX() is very powerful but it can become tricky to understand how it will behave in complex scenarios, with multiple filters, and particularly when you start nesting SUMX() within other SUMX().

    The other thing I would do to simplify is remove the COUNTROWS() part because it's not impacting anything as is.  The FILTER() returns the same table with or without it. You can test this by simply replacing the entire COUNTROWS() section with any random number.  It won't impact the result.  FILTER() wants a Boolean expression for its condition(s).  Maybe I am misunderstanding what you are trying to accomplish with that part, but as it stands, it isn't changing anything.

    All that being said, you might try a measure like this for Measure 1:

    =
    IF (
        [Usage],
        CALCULATE ( [Usage], FILTER ( ALL ( Index[Index] ), Index[Index] = -1 ) )
    )
    

    I believe this should give you the same result as your original Measure 1 but I think its a little easier to understand the mechanics.

    Then simply use this measure for the CorrectTotals measure above.

    One thing to note when using SUMX() (or any other X iterator function) is that the result may vary depending on the final pivot.  Not that the math is different, but if the goal is to make the totals appear to be the actual totals of the rows being shown on the pivot, you must make sure you are iterating over the same table/column showing in your pivot.  Just something to keep in mind if your layout changes.

    Tuesday, June 7, 2016 10:22 PM
    Answerer

All replies

  • With Power Pivot, the cells calculate individually which is a little counter intuitive when viewing a pivot table because it makes you think that Totals are really the totals of the numbers displaying above them.  Sometimes that's true, but when the filter logic becomes more complex or you begin incorporating ratios into your calculations, that no longer applies.

    Basically that means that each cell in the total rows is calculated on its own regardless of the rows above it.  So the filters from the pivot table being passed to your measure are all ID's (rows) and all Indexes (columns).

    To force the pivot to show the "correct" total based on individual rows, you must iterate your measure over those rows and add up the final result.  This is what SUMX() will do.

    So to get the result you are looking for with your existing measures and current pivot table layout, you want something like this:

    CorrectTotals=
    SUMX(
        VALUES('Fact'[ID]), 
        [measure 1]
    )

    This is saying, "Calculate the result of Measure 1 for each unique value of ID, then at the end, sum the results of those calculations."

    That will work but you should probably consider taking it a step further to simplify your Measure 1.  SUMX() is very powerful but it can become tricky to understand how it will behave in complex scenarios, with multiple filters, and particularly when you start nesting SUMX() within other SUMX().

    The other thing I would do to simplify is remove the COUNTROWS() part because it's not impacting anything as is.  The FILTER() returns the same table with or without it. You can test this by simply replacing the entire COUNTROWS() section with any random number.  It won't impact the result.  FILTER() wants a Boolean expression for its condition(s).  Maybe I am misunderstanding what you are trying to accomplish with that part, but as it stands, it isn't changing anything.

    All that being said, you might try a measure like this for Measure 1:

    =
    IF (
        [Usage],
        CALCULATE ( [Usage], FILTER ( ALL ( Index[Index] ), Index[Index] = -1 ) )
    )
    

    I believe this should give you the same result as your original Measure 1 but I think its a little easier to understand the mechanics.

    Then simply use this measure for the CorrectTotals measure above.

    One thing to note when using SUMX() (or any other X iterator function) is that the result may vary depending on the final pivot.  Not that the math is different, but if the goal is to make the totals appear to be the actual totals of the rows being shown on the pivot, you must make sure you are iterating over the same table/column showing in your pivot.  Just something to keep in mind if your layout changes.

    Tuesday, June 7, 2016 10:22 PM
    Answerer
  • I like that solution.  Thank you.  I solved right before I saw your answer with the following.

    Measure 3:=sumx(
    ADDCOLUMNS(
    SUMMARIZE(Fact,'Fact'[id],Index[Index]),"New",
    CALCULATE([usage],all(index[index]),Index[Index]=-1)),[new])

    This got me the results i was looking for and makes sense to me as it is similar to how a SQL Group By would work.  It amazes me how many ways there are two solve the same problem.  I will go into DAX Studio and look at the efficiency of each result because my fact table is quite large.  

    Thanks very much for taking the time to explain what was going on there. Sometimes DAX feels like magic.

    Wednesday, June 8, 2016 5:36 PM
  • Can you expand on ‘iterating over the same table/column’ ? 

    I am looking for a set of rules that will help me limit the surprises when users are changing the pivot table layouts.   


    Friday, October 20, 2017 3:25 PM