Home Forums Power Pivot Cumulative sum in PowerPivot

This topic contains 8 replies, has 2 voices, and was last updated by  Maxi 1 year, 11 months ago.

Viewing 9 posts - 1 through 9 (of 9 total)
  • Author
    Posts
  • #4854

    Maxi
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    Hi everybody,

    Tried for a while now, but I couldn’t solve the following issue:

    I have a data table in Excel, which looks like the following:

    Data Source Table

    What I’d like to have is this:

    Output from PowerPivot

    I encountered the following issue:

    • When I filter for the current YearMonth (in my example 201602), the row “Apple” disappears, as there is no sales of apples in 201602. However, there is a history of sales for apples, which are cumulated (Column: Sales of Country to Date). Also, there is a forecast, which is the total of past sales and future sales of apples (Colomn: Sales of Country Forecast).

    Thank you in advance.

    #4862

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    Hi,

    There at least a couple of ways to achieve your target report. However, if your current question is to find a way of preventing products on rows from disappearing when all measures in a pivot return blank (a default behavior), I recommend that you set the pivot table options “Show Items with No Data on Rows” and/or “Show Items with No Data on Columns” under the Display tab of the Pivot Table Options dialog.

    Tom

    #4868

    Maxi
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    Tom, thanks for your reply! Unfortunately, that didn’t solve my problem. The row still disappears…

    Any other possible way to solve this? Any help is very much appreciated! Thanks!

    #4869

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    Can you attach some sample data in addition to the pivot you are working with?

    #4870

    Maxi
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    Sure, here you go.

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

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    Maxi,

    Please see workbooks with two solutions.

    1) Multi-table model, plus option to display items with no data on rows.
    2) Cube Formula report

    I used power query to quickly create a multi-table model from your single table. The benefits of multi-table modeling, which harnesses the power of the power pivot engine are discussed in “Power Pivot and Power BI” by Rob Collie and Avi Singh. If this data model were for production, I would have created ID columns for country and product and based the relationships on the ID columns instead of the complete names. I also would have created a dates table to take advantage of the built-in time intelligence functionality for To-Date measures.

    For an excellent discussion of cube formulas, their strengths and weaknesses, when used with the power pivot data model, please see section A2 (Cube Formulas – the End of GetPivotData) of Power Pivot and Power BI.

    Both multi-table modeling and cube formulas are presented in thorough detail in Rob Collie’s online Comprehensive Power Pivot Course.

    Some other topics extensively covered in the on-line course include:

    Learning how to think like the formula engine (understanding relationships, plus how filter and row context are applied)
    DAX functions (basic to advanced)
    Portable formulas and their benefits
    Time Intelligence (built-in and custom)
    Iterators (XFunctions)
    Many-to-Many relationships
    Responding to Excel-side slicer selections using disconnected tables within Power Pivot
    Two sides of filtering (table purpose and filter purpose)
    Using macros to automate repetitive tasks while working with pivot tables and pivot charts.

    The course is taught by Rob Collie, who not only presents the material, but adds insight and adventure into the learning process.

    The course does not teach Power Query, Power BI Desktop or Power BI service, although practically all of the DAX you learn in the course is extensible into the Power BI domain.

    I have taken the on-line course and it was a game changer for me.

    Please feel free to ask questions about the attached workbook or online course.

    Tom

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

    Maxi
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    Tom,

    thank you very much for your suggested solutions.

    Maybe I have expressed my problem in a difficult way, but the numbers in the row “apple” are still missing. It should not only show the row but also show the Sales to Date (3600 in this case).

    I’ll check out the course, when I have some time to spare.

    Thanks

    #4927

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2244
    • Total: 2253

    Maxi,

    My apologies for not listening well. Please see attached workbook with a revised lifetime to date measure.

    What method do you want to use for forecast?

    Tom

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

    Maxi
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    Tom,

    Thank you very much for your help! This works like a charm. Now I understand, that my datamodel was not designed right.

    For my forecast, I will use the same logic, without the YearMonth-Filter.

    Thanks once again!

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

You must be logged in to reply to this topic.