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)
ForumBlendQ.twbx 82.8 KB
The first example works but the second example doesn't. I want the expenses for that month divided by the sales for that month (not the total sales)
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
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
ForumBlendQ.twbx 124.2 KB
Actually now that I read over your post, I think this works. The blend works for the monthly information and the non-blend allows me to get the overall average by category. I don't need to see if there are 0 expenses. Thanks!
That was fantastic actually.
No worries - glad it helped.