Home Forums Power Pivot Correct running totals in a pivot table

This topic contains 3 replies, has 2 voices, and was last updated by  tomallan 4 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #9600

    jazzista1967
    Participant
    • Started: 8
    • Replies: 20
    • Total: 28

    Hello. I am trying to run a cumulative formula in a pivot table however I am not getting the results I am expecting: I am getting the correct results at the end of each month but not in the cumulative column (Column E). Can somebody take a look at my formula. Thanks in advance

     

    Attachments:
    You must be logged in to view attached files.
    #9602

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2377
    • Total: 2386

    Your formula for running total does not include how the formula engine should handle the description field.

    #9603

    jazzista1967
    Participant
    • Started: 8
    • Replies: 20
    • Total: 28

    Hi Tomallan. Thanks for your reply. So how should change the formula so it works correctly? Regards

    #9608

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2377
    • Total: 2386

    I want to open this up to a discussion.

    The forum participant is asking for a formula that works like the built-in “to date” functions, but where the to-date total can be further sub-divided by a non-date-related column.

    My experience in working with the built in time intelligence, is that if one uses the to-date functions where the values at the date level will be further sub-divided (in this case by a text description field), the to-date functions will not work.

    Also, since values in Power Pivot cells have no reference to any other cells, to create a such custom total with DAX would require an assumption about the pivot table order for the text column (whether its values ascend or descend). I think that such an assumption (and the resulting running total) would be invalidated as soon as a user changed the display order of text column in the pivot (non an unusual user interaction).

    In my opinion, the best solution to this request would be to use Power Query (Get & Transform) to group and shape the data to the year-month-description level and, after loading this summarized table to an Excel worksheet, manually add the “to-date” calculations with regular Excel formulas.

    What think you?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.