So i have a data set with fields: shop names and sales from those shops and sales per category of a product in every shop. I want to create a pie chart with only two sections: Product Category A and Product Category B, which would sum the sales for each of these categories from all the selected shops in the filter. So no matter how many shops i select the pie chart is always divided only in two sections.
When i use shop as a dimension, the pie chart is divided into as many sections as the number of shops i have. Instead I need the pie chart to be divided into two sections only. one section should show the sum of all the sales in all shops for Category A, the other section should show the sum of sales in all shops for Category B.
The question is, i guess, how do i create a dimension that contains the two categories, and a measurement for those categories?
but i do not have a field that contains the categories. So i have Table 1, with sales on fruits for example, where i have apples, oranges etc. Then i have table 2 with sales on vegetables: carrots, tomatoes etc.
Now category A - apples
category B - carrots.
now i need a pie chart, to see what amount of income is delivered from carrots and how much i got from apples.