Best Of

If you were doing only one bar each year, you could use a Bullet chart which lets you use a beast mode to set a goal. For how you want to set it up, my thought is to do either a line and bar chart or a symbol and bar chart and use a beast mode to calculate your major and minor goal lines for you lines/symbols.

Are your 0's in your data actually 0 or are they null (Ex: row 'a' column '2000' in your example). If they are null then you will need to do IFNULL('Column',0) for each of your columns in the beast mode, or use your ETL to change the nulls to 0. This is the only thing I can think of at the moment.

@Taichi

I agree with @mhouston 's suggestion. To add to it, you could use ETL to recreate the respective pivot tables and then append or join them both together.

You can't combine two cards into one. If you have a revenue dataset and an expense dataset, you can append them and then you can build a card that has both revenue and expenses.

It's going to be a little tricky, because doing it as a pivot table, your beast mode is basically going to be based off of the dataset and affecting that, and not the pivot table directly. If you're fine with the renaming the Grand Total Column to be your Collection Total column, and you're ok with the '8000' column showing as negative, then you could do the beast mode above and use the beast mode as your values instead of Sum of Net. I've attached your file that I modified with what this would look like on the 'New' tab, with Collection Total being the equivalent of the beast mode.

But if you want it as a separate column, then I think what you'd have to do is do a pivot in MagicETL so you're dataset is in the pivot format to begin with, and then you could do a beast mode that adds each column but subtracts the 8000 column.

I don't think there's a way to do this, which seems a little odd. I'd use 'Feedback' to suggest it to Domo as well as post it in Ideas Exchange. I'll definitely upvote it if you do!