Pivot table from power pivot : show items without data ?

[Lolo]

Hello,

I have a power pivot model and I have created a pivot table on it, on Excel.
I realize that I cannot display easily the items without data, when I filter.

The option is greyed out in the pivot filed settings.

After some research I understand I need to manage it in powerpivot itself inside measures I could create.

But it is beyond my current power pivot level
I don't manage to create a measure that could display data with empty values, and only depending on the filters applied on slicers.

See attached file, I have created a little example of the problem and the expected result

Hope someone will help

Thank you in advance.

 

[Chihiro]

In this instance. It's much easier to do most of data transformation at PowerQuery stage rather than in PowerPivot.

Once both tables are loaded. Merge Table2 to Table1 using Full Outer join.

Expand all columns in New Column.

Add custom column with following formula.

= if [CITY] = null then [CITY.1] else [CITY]

Remove [CITY] & [CITY.1]. And rename custom column to CITY.

Right click on [STOCK] and replace null with 0.

Ensure all columns are in appropriate data type.

Load to PowerPivot and report.

Sample output.

 

[Lolo]

Thank you for answer, I will check and revert !
Nb: Sorry for late reply I was in holidays

 

[Lolo]

OK clear I have reproduced. Good idea to report the 0 stock lines in the FACT table, in order to be able to simply manipulate them.

however, I'm wondering if this is usable on a table with a lot of data (fact : 300 000 lines). Because the full outer join will generate a lot of lines. I will check.

Thank you for your help !