Column/LIne chart question

[whereismydata]

Hi,

again, something that first seemes simple but then is challenging:

I want to visualize income/cost/profit of different products in one visual.

where the red line is 0, values below are cost, above income, grey line is profit, X axis =  date, Y axis = values

currently I've got one table where all data is stored. costs are negativ values:

| DATE | Product | Category 3 | Category 2 | Category 1 |

What I want to achieve:

I want to drill down on the categories. Currently I can only drill down on date. Idealy I would like to first show category 1, than drill down to Category 2, then 3, also drill down on dates should still be possible.

I pretty flexible in data modelling. One solution I could think of would be putting all aggregation levels into one table like this:

Date	Product	Category_Name	Category_Level	Value
2021-01-01	Product A	A	3	1
2021-01-01	Product A	B	3	2
2021-01-01	Product A	C	3	4
2021-01-01	Product A	D	2	3
2021-01-01	Product A	E	2	4
2021-01-01	Product A	F	1	7

This, I think, would allow me to then filter on the Category level.

But is there a more elegant solution to this problem?

Thank you very much!

[Icey · Accepted solution]

Hi @whereismydata ,

Try something like this:

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[whereismydata]

Hi @lbendlin 

thank you for taking time to look at my question.

regarding you answer:
using a hierarchy was the first think I tried but couldn't get to work. In the column series section I only can insert one field:

[lbendlin]

You don't really want to include hard coded aggregations if you can achieve the same from your raw data. So if you have data on the lowest granularity level (day and Category 3, I guess) you should then be able to add hierarchies as needed.