June 7, 2016 at 9:34 am #4854
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:
What I’d like to have is this:
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.June 7, 2016 at 6:19 pm #4862
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.
TomJune 8, 2016 at 7:24 am #4868
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!June 8, 2016 at 8:14 am #4869
Can you attach some sample data in addition to the pivot you are working with?June 8, 2016 at 9:00 am #4870
Sure, here you go.June 8, 2016 at 6:03 pm #4874
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)
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.
TomJune 13, 2016 at 8:37 am #4922
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.
ThanksJune 13, 2016 at 6:26 pm #4927
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?
TomJune 14, 2016 at 10:00 am #4936
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!
You must be logged in to reply to this topic.