show items with no data - only on specific colums in a pivot table

[MaartenJ]

A question; Is there any way to make the setting 'show items with no data' apply only to specific columns in a pivot table? Right now the setting applies to all the column in the table.

The problem I run into is that I'm building a report on a fairly complex data set. When I select the 'show items with no data' option, the table immediately gets bloated with 80% empty rows, since the sum that I'm calculating is depending on 6 or 7 colums, all with multiple possible entries. I just want to show the empty items for 1 of the columns.

[vanessafvg]

@MaartenJ what is it that you are wanting to show?  can you give more information about the content?

[MaartenJ]

@vanessafvg I'll try to summarize the content I have (english is not my mother tongue, but I'll try to explain as best as possible).

the main fact table is a table with financial transactions. Every transaction is linked to the combination of Project and a Subproject.

all financial transactions are categorized by cost type, ERPID etc etc.

The following occurs; within the department that I'm active, not all subproject are the same. Some are connected to registrations/workorders (and thus, have a current status (created, planned, fixed, repaired, closed)), and some aren't.

Financially all the subprojects have te same form.

I've received the request to report the total of the financial transactions, specified by project, subproject, registration-status (if available) and cost type.

When I add the registration-status to the pivot table, only subprojects that are connected with a registration show up in the pivot table, and I miss about 40% of the total financial transactions. When I select 'show items with no data' my pivot table is filled with 80-90% empty rows, since it will create an empty row for every possible combination of project, subproject, registration-status, cost type etc.

Only solution I see right now is have my load-script join the table containing the registrations to the table containing my transactions, and filling a value for registration-status there, so that I force that column to always have a value.

[vanessafvg · Accepted solution]

@MaartenJ that makes sense, you will need some kind of indicator that will allow you to select all the records you needs.  So if you can add some indicator, possibliy in power query based on the rules you mention you should be able to just pull those records.

[MaartenJ]

@vanessafvg Got it to work. Thanks.

[skrahman]

Hi,

Would be keen to know how made it to work...I'm faced with a similar issue for which I can't get a solution anywhere.

[MaartenJ]

@skrahman Basically what I did;

I added a column to my main fact table (subprojects in my case), and used a formula to make sure there was a value for all rows (so in my case, if a value for ERPID was present, I gave it that value, else a text value 'no ERPID available'.

I used this new colum in my tables and visuals.

[MaartenJ]

@vanessafvg Yeh, I figured as much. Would have been nice if the 'items with no data' option could be set per column though.

I'll see if I can get it up and running.