Is it possible to modify the Primary datasource at your end? If yes, you can try this:-
1) Replicate Primary with an added column of the required tags like this:
Main Datasource id value Tag 1 10 Profit 2 20 Profit 3 30 Profit 1 10 Discount 2 20 Discount 3 30 Discount 1 10 quantity 2 20 quantity 3 30 quantity
Your secondary datasource using which you are creating icons looks like this:
Secondary Data Source Tag profit discount quantity
2) Create Data relationship on Tag
3) Create a calculated field in Primary for the charts to display:
if Tag=Profit then compute profit
elseif Tag=discount then compute discount
else compute quantity
4) Use Filter actions on the dashboard to filter using Secondary datasource icons
This would mean changing the data structure and I wouldn't need the secondary data source as I could just use that tag as the dimension.
I was trying to avoid pivotting the data as I have many rows!
Thanks for your feedback though!
1 of 2 people found this helpful
Can you try with sheet swap.
Refer my sample workbook.
Thanks for the suggestion but this doesn't solve my problem as it is about the chart type and not the data.
I like the approach and I am trying to understand how you have achieved, my only concern at the moment is that I don't think the data is correct. If I look at one state in particular e.g. Texas, profit according to your map is -25,729 whereas if I calculate on a new worksheet the answer is -128,647.
What I am trying to understand is how the KPI dimension has been assigned to each row without pivotting the data.
Can you explain how this works and if it is showing the accurate data?
Okay, this is what I did:
In the data source I did a cross DB-Join:
The orders look like the left table, the "Tabelle1" is like the right table:
If you do a join via the Tag-Column, you will get the same line multiple times:
The KPI-Value-Column has this calculation:
So for each line will only be one valid value. So for this datasource you can't use the "normal" KPI-Columns, as These will have multiples inside.
I hope this will help you.
I had wanted to avoid pivotting the data as I already have a huge number of rows, but thank you for explaining your solution. This is an option if I can increase the rows!