Hi Alex

If you remove the link to blend on month you should get your orders column to contain the same number 84399 throughout, and your 'order per' calc will re-adjust.  See the attached I set up in v 2018.1.  1 sheet has the data with the blend, the other without

You will get a warning message but the values still display

No blend link

blend on month, not all categories have expenses in a month

When you say you want to do this on a monthly level too, if you're still looking to divide by total sales for each month (ie 84399), then you can again just remove the blend link on month

in the example below, you can see Feb has no expenses for Penciles, so no row is shown

If this isn't what you're after, then please provide a packaged workbook with a simplified example in it (like I've provided) and more detail on exactly what numbers you're expecting when.

Packaged workbook with above examples attached (v2018.1)

Many thanks

Donna

I wondered if that was the case, but wasn't sure since you stated that your expenses doesn't always have a value for every category in the month, so the value in this instance I guess is 0....

FIrst off, using my example, if you apply the blend link on month and then select the date pill to 'show missing values', you'll see the missing rows displayed and the amount of sales based on sales in the month

As there is 'missing data' for this row, as opposed to the row existing with a NULL value, then the data displayed is just 'empty'.

If you want to get 0 displayed, you'll need to do the following :

Create a new field based on the Expenses : Amount field

ZN(LOOKUP(SUM([Amount]),0))

and then base your monthly category per sales calc on that field instead.

This is basically using a table calculation to 'look up itself' and replace with 0 in the event a value doesn't exist.

Updated workbook v 2018.1 attached

Donna

No worries - glad it helped.

Donna