Interactive Dashboard chart from multiple splicers

[Liphor]

I have created 3 different charts of the same type from 3 different sets of data. I create them by using pivot table and splicer. How do I transform them into a single  dashboard chart but able to show the different chart interactively one at a time. I also intend to add more data sets to this chart .Attached is my workbook .

[#2 · Accepted solution]

@Liphor Something like in the attached file perhaps? It's just a very rough model, based on your data and done with Power Pivot and would  require further refining though. Use the slicers to select the year and region.

[#3]

Your proposed solution looks good, However because I have no knowledge of Power Pivot so I have to spend a couple of days to learn about this function. Where is in located in the excel menu. I am currently using excel 2016 and 2021 on my laptops. I would also like to ask whether I could add additional line like cumulative and mean values in this graph. Thank you for introducing me to power pivot.

[#4]

@Liphor Not sure about 2016, but in 2021 you should see Power Pivot in the top menu as shown in the top right-hand corner of the picture below.

[#5]

May I know are the tables link by connection or pivot table.

[#6]

@Liphor In this particular case I connected to each of the rainfall tables with Power Query, where I transformed them to a usable format and in one table. Then loaded the single table into the Data model (i.e. Power Pivot) from which the Pivot Chart was created.

All was intended to demonstrate what's possible, but as said it's very rough and if I would start from scratch I would probable do it differently. I.e. create a single (tabular) database from the start, use proper dates rather than year numbers and short codes for the months.

[#7]

Is it possible to show me how would I create a single data base from the 3 tables. I mean how would the matrices look like. Can my database be expanded over time. For your info the dates I have are just years and months.

[#8]

@Liphor That depends on where the data comes from. Is it an on-line source (a web page), a CSV file or do you get these tables ready made in Excel files? Can you show where it comes from and how the raw data looks like? Am assuming you don't type it all in manually.

[#9]

Data is basically a compiled table in xls. I am not using raw data as I am assuming the people who compile the data has verify the raw data. The data is the same as the one I upload earlier.

[#10]

@Liphor Well, you would need to clean-up the tables. Make sure the headers are exactly the same. In your original file they are not. 

As I said earlier, I would create one table with all data. Done that in the Data sheet. Then, I also made sure that the we are working with real dates to begin with. It becomes easier that way to work with some data intelligence in PQ and PP. From that point, it's not very difficult to create a pivot chart with slicers. See attached file in the Chart tab. This version is more robust than the one I did in my previous file.