locked
Measure to Cume a Quantity RRS feed

  • Question

  • Using this data ... 

    Fruit,Date,Qty
    Apples,41669,10
    Oranges,41683,20
    Apples,41744,30
    Lemons,41654,40
    Apples,41764,50
    Lemons,41689,60
    Lemons,41775,70
    Oranges,41702,15
    Oranges,41737,90

    ... and, this measure ... 

    Cume of Qty:=SUMX (
        CALCULATETABLE (
            ADDCOLUMNS (
                Table1,
                "CumeQty", CALCULATE (
                    SUM ( [Qty] ),
                    FILTER (
                        Table1,
                        EARLIER ( [Date] ) >= [Date]
                            && EARLIER ( [Fruit] ) = [Fruit]
                    )
                )
            )
        ),
        [CumeQty]
    )

    ... does anyone know why I'm getting these results instead of those shown in red? ...


    Tuesday, October 28, 2014 6:54 PM

Answers

  • Your references to Table1 are within the filter context of the pivot table. 

    I am not entirely sure why you're looking for 140 in your Grand Total, but I'll accept that as the correct and here's the modification to your measure to fix the problem:

    Cume of Qty:=SUMX (
        ADDCOLUMNS (
            Table1,
            "CumeQty", CALCULATE (
                SUM ( [Qty] ),
                FILTER (
                    ALL( Table1 ),
                    EARLIER ( [Date] ) >= [Date]
                        && EARLIER ( [Fruit] ) = [Fruit]
                )
            )
        )
    ,[CumeQty]
    )

    When you refer to Table1, it is affected by the pivot's filter context. On each of the non-total pivot rows, the row label (also referred to as a rowfilter) is part of the filter context. Thus, when you refer to Table1 in your FILTER() function, you are filtering a table that is already filtered to [Fruit] = "Apples" and [Date] = 1/30/2014 (or the other dates on the other rows in your example).

    By using ALL() we strip that context and filter the entire table. Since you already have your FILTER() set up to maintain the row context from your earlier reference to Table1 (also constrained by pivot filter context), this works just fine.

    I've also removed your CALCULATETABLE() call because it was doing nothing.

    • Marked as answer by Mark Weisman Tuesday, October 28, 2014 9:05 PM
    Tuesday, October 28, 2014 8:02 PM

All replies

  • Your references to Table1 are within the filter context of the pivot table. 

    I am not entirely sure why you're looking for 140 in your Grand Total, but I'll accept that as the correct and here's the modification to your measure to fix the problem:

    Cume of Qty:=SUMX (
        ADDCOLUMNS (
            Table1,
            "CumeQty", CALCULATE (
                SUM ( [Qty] ),
                FILTER (
                    ALL( Table1 ),
                    EARLIER ( [Date] ) >= [Date]
                        && EARLIER ( [Fruit] ) = [Fruit]
                )
            )
        )
    ,[CumeQty]
    )

    When you refer to Table1, it is affected by the pivot's filter context. On each of the non-total pivot rows, the row label (also referred to as a rowfilter) is part of the filter context. Thus, when you refer to Table1 in your FILTER() function, you are filtering a table that is already filtered to [Fruit] = "Apples" and [Date] = 1/30/2014 (or the other dates on the other rows in your example).

    By using ALL() we strip that context and filter the entire table. Since you already have your FILTER() set up to maintain the row context from your earlier reference to Table1 (also constrained by pivot filter context), this works just fine.

    I've also removed your CALCULATETABLE() call because it was doing nothing.

    • Marked as answer by Mark Weisman Tuesday, October 28, 2014 9:05 PM
    Tuesday, October 28, 2014 8:02 PM
  • Here is another way to do this. 

    Cumulative Sum of Qty :=
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER (
            ALL ( Table1[Date] ),
            Table1[Date] <= MAX ( Table1[Date] )
        )
    )

    Here are the results


    Regards, Avi www.powerpivotpro.com

    Tuesday, October 28, 2014 8:16 PM
  • Avi, I'm leaning towards Greg's answer (above) only because I don't intend to expose this measure to Client Tools.  I just need it for use with other measures, and in that context I need to enforce the Fruit relationship in the data model itself, and not rely on Pivot Table filtering.   I only want to cume quantities for a given Fruit across time.

    I appreciate your assistance, and I apologize if my use of a Pivot Table summary to illustrate my problem led you astray.

    Do you have anything to add to my thinking?  


    Tuesday, October 28, 2014 8:39 PM
  • Greg, great explanation!  I've made your recommended changes and I'm pleased with the results.
    Tuesday, October 28, 2014 8:41 PM
  • Avichal, these two measures are not equivalent. They look very similar, but return different grand totals and are performing a very different calculation under the hood.

    I'll compare the modified version I replied with and the To-Date version you have supplied:

    Cume of Qty:=SUMX (
        ADDCOLUMNS (
            Table1,
            "CumeQty", CALCULATE (
                SUM ( [Qty] ),
                FILTER (
                    ALL( Table1 ),
                    EARLIER ( [Date] ) >= [Date]
                        && EARLIER ( [Fruit] ) = [Fruit]
                )
            )
        )
    ,[CumeQty]
    )
    Cumulative Sum of Qty :=
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER (
            ALL ( Table1[Date] ),
            Table1[Date] <= MAX ( Table1[Date] )
        )
    )

    In the first we are using SUMX() and iterating over a table.

    Let's look at what this does at two different points in the pivot table (I'll use the Apples example in the original post and the first one listed in yours).

    In pivot table row one (date = 1/30/2014):

    We are adding columns to Table1. In this case, Table1 is affected by our pivot context and is actually a single row table:

    Fruit   | Date       | Qty
    Apples  | 1/30/2014  | 10

    The column that we add is defined by this section:

            CALCULATE (
                SUM ( [Qty] ),
                FILTER (
                    ALL( Table1 ),
                    EARLIER ( [Date] ) >= [Date]
                        && EARLIER ( [Fruit] ) = [Fruit]
                )
            )

    This is implementing an expression nearly identical to the To Date total that you are suggesting as a measure, but with EARLIER() since it is referring to the row context of the earlier invocation of Table1.

    The filter is making sure that the same [Fruit] is considered, and that all dates are less than or equal to the one in the first invocation of Table1.

    The resulting table after ADDCOLUMNS() looks like this:

    Fruit	| Date		| Qty	| CumeQty
    Apples	| 1/30/2014	| 10	| 10

    Thus, at the date level of the pivot table (and with only one fruit in context) this performs a much more computationally intensive version of your To Date measure which simply sums [Qty] across all the dates less than or equal to the current date.

    Now let's look at the Grand Total level:

    At this level, our first invocation of Table1 returns a 3-row table:

    Fruit	| Date		| Qty
    Apples	| 1/30/2014	| 10
    Apples	| 4/15/2014	| 30
    Apples	| 5/5/2014 	| 50

    After ADDCOLUMNS() our table looks like this:

    Fruit	| Date		| Qty	| CumeQty
    Apples	| 1/30/2014	| 10	| 10
    Apples	| 4/15/2014	| 30	| 40
    Apples	| 5/5/2014	| 50	| 90

    This is identical to the pivot table, as should be expected since we've implemented a filtered to date expression. Where the difference comes out is when SUMX() steps through this table adding up the values of [CumeQty]. The simple To Date measure you've supplied gives the same result at the Grand Total level as at the latest date in the pivot. The SUMX() version, though adds up each of the separate to date values and provides a grand total, not of 90, but of 140.

    This exact implementation will look wonky if you remove the filter context on [Fruit]:

    Fruit	All
    	
    Row Labels	Cume of Qty
    1/15/2014	40
    1/30/2014	10
    2/13/2014	20
    2/19/2014	100
    3/4/2014	35
    4/8/2014	125
    4/15/2014	40
    5/5/2014	90
    5/16/2014	170
    Grand Total	630

    At each row, of the table, there is only one fruit in context, since no fruits share a date (this is not a problem though, since the iterators step through the table returned and don't care how many distinct values there are in any column).

    An interesting thing to look at is to add one more row to the source:

    Fruit	| Date		| Qty
    Lemons	| 1/15/2014   	| 40
    Apples	| 1/30/2014   	| 10
    Oranges	| 2/13/2014   	| 20
    Lemons	| 2/19/2014   	| 60
    Oranges	| 3/4/2014   	| 15
    Oranges	| 4/8/2014	| 90
    Apples	| 4/15/2014	| 30
    Apples	| 5/5/2014	| 50
    Lemons	| 5/16/2014	| 70
    Apples	| 1/15/2014	| 5

    I've added an entry for Apples on 1/15/2014. If you add this to the table, the value for 1/15/2014 is correct (45), and each date's value afterward is the same as before, except for Apple's. At the Grand Total level, though, we see a jump of 15, not by 5. This is because at each level of the table returned by ADDCOLUMNS(), at the Grand Total level, there are 3 dates after 1/15/2015 for Apple, each of which is increased by 5.

    Based on the desired numbers in the original post, we can't implement the much simpler To Date pattern you've supplied.


    • Edited by greggyb Tuesday, October 28, 2014 8:50 PM formatting
    Tuesday, October 28, 2014 8:46 PM
  • Avi, I'm leaning towards Greg's answer (above) only because I don't intend to expose this measure to Client Tools.  I just need it for use with other measures, and in that context I need to enforce the Fruit relationship in the data model itself, and not rely on Pivot Table filtering.   I only want to cume quantities for a given Fruit across time.

    I appreciate your assistance, and I apologize if my use of a Pivot Table summary to illustrate my problem led you astray.

    Do you have anything to add to my thinking?  


    Mark, based on the explanation you've provided here, it may be superior to implement something more similar to Avichal's solution - it is unclear to me whether you require the special Grand Total behavior that the SUMX() provides. If you do not that specific behavior, the SUMX() will always be an inferior and slower solution (across a large fact table it could get quite slow - SUMX() and FILTER() are both iterators and will run slowly when nested).

    Without seeing the larger model in which you are implementing this, and the other measures, I cannot say for certain how to implement your filtering, but there is likely a more efficient way than the nested SUMX( FILTER() )

    Tuesday, October 28, 2014 9:15 PM
  • Greg, I appreciate the nuances to which you have alluded above.  As of now, I'm dealing with about 1.6M records, and less than 15 columns.  Since I want to restrict my cumulative total to a particular Fruit (Part Id in my real world case), I think Avi's code (below) needs to be modified to enforce that Fruit context [as I'm doing now with EARLIER() ].  Am I correct in believing that Fruit context can't be enforced in my data model without EARLIER() and ADDCOLUMNS()?

    Cume of Qty :=

    CALCULATE (
        SUM ( [Qty] ),
        FILTER (
            ALL ( Table1 ),
            [Date] <= MAX ( [Date] )
        )
    )

    Tuesday, October 28, 2014 9:48 PM
  • I can't answer that definitively without knowing more about the model and the context in which the measures will be evaluated.

    As an example, if there it some sort of filter context (in an outer CALCULATE() or coming from the pivot table), then you could simply do this:

    FILTER( ALL( Table1[Date] )
        , Table1[Date] <= MAX( Table1[Date] )
    )

    This selectively strips filter context only from [Date], while preserving all other filter context.

    Since you said you are using your cumulative measure in conjunction with other measures, but did not specify whether it is contained within another measure (as opposed to doing arithmetic with other measures) I can't know what sort of additional row and filter contexts you are operating in.

    My hunch is that there is a more elegant solution than SUMX(), unless you need the specific behavior I described for Grand Total results.

    Tuesday, October 28, 2014 10:04 PM
  • (Greg - you are a DAX Ninja! Thanks for the explanation. I finally paid some attention and learned a lot!)

    Mark, 
     Am I correct in believing that Fruit context can't be enforced in my data model without EARLIER() and ADDCOLUMNS()?

    If the Fruit Context is coming in via the Pivot then you do not need anything extra to enforce the context.If a measure is invalid (or you just don't want to show it) when more than one Fruit (PartID) is selected, you could check for that and return BLANK()


    Say

    Cumulative Qty for Single Fruit Only :=
    IF (
        HASONEFILTER ( Table1[Fruit] ),
        CALCULATE (
            SUM ( Table1[Qty] ),
            FILTER (
                ALL ( Table1[Date] ),
                Table1[Date] <= MAX ( Table1[Date] )
            )
        ),
        BLANK ()
    )



    Regards, Avi www.powerpivotpro.com

    Tuesday, October 28, 2014 10:39 PM
  • A note to expand on Avichal's post above (which illustrates a very good pattern to learn for suppressing measures), the pivot table context can come from [Fruit] anywhere in the pivot table, whether it be a slicer as shown above, or a table filter as you (Mark) have implemented in the original post, or as row/column headers. All of these create filter context.

    Slicers and filters create context for an entire pivot, whereas row and column labels provide context in their respective rows/columns (grand totals are missing either one or both elements of filter context, which can cause measure issues depending on what you're trying to implement).

    Tuesday, October 28, 2014 11:14 PM