Treat no field data combination as a 0 in a calculated measure

[vbavirgin]

I have a large data set that has data by Region, location, period, SKU, Stock. The source data does not hold a row record when the stock = 0.

Region A, location A, SKU A has a stock record for Period 1 and Period 2
Region A, location B, SKU A has a stock record for Period 1 only

Region and SKU fields are in common to to both locations.

I need any calucualtions of pivot tables to assume that:
Region A, location B, SKU A has a stock record for Period 2 = 0

I can amend a calulated measure to ensure that any totals in a pivot table are correct e.g. count of stock records includes the missing row data by using a DistinctCount of Region and SKU period.

However, I need my calulations to work in a pivot table context to assume that a no row record is a 0.

Region	SKU	Location	Period 1 (stock)	Period 2 (stock)
A	A	A	2	1
A	A	B	1	No Data but I want = 0
Total Count Stock Record			2	=1 but I want = 2

I have tried using Power Query to add additional rows for any missing combinations or region, SKU, Period but this creates an unmanagleable amount of rows.

Is there another way I can do this (hoping there is some clever trick!)?

 

[saboh12617]

Hello,

As i understand, at the end your count is just the amount of rows in the table. You could use a COUNTA on the first column of the table to get your count.

If the problem is about the pivot table, for each period, you can add a new column to the table with a little formula like so

=MAX(0;[@[Period 1 (stock)]])

And use them instead of the original ones.

 

[vbavirgin]

Hi. Thanks for the response. In this simple example this would work but I ideally want the data model to be able to provide a 0 value for stock even if these is no row data.

 

[RoryA]

Measures don't get calculated for data combinations that don't exist. Couldn't you just set the pivot table to display 0 for blanks?