Stacked Bar Chart using Metrics and multiple data sets

EliseB

Hello, I am struggling to gather data together from multiple data sets to represent as a stacked bar chart. I have large spreadsheets that use dropdown options for data I need such as customer journey and impact rating (high, medium, low) there is a lot of other data in these sheets as well and they are over the 100,000 limit so I am trying to be specific when inserting a data reference (difficult with how the sheets are set up that is out of my control).

Based on things I have read here I have created a metric sheet where I need to pull agency specific information and use COUNTIFS formulas to count the number of High, Medium and low impacts per customer journey. I am struggling to pull the data and a little frustrated that all this work is required for one tile on a dashboard that then needs to be duplicated for several agencies.

I am pretty sure I am super off with the formula and have spent too much time trying to work it out!

Can someone please help me?

Thanks,

Elise

Genevieve P.

Hi @EliseB

If I'm understanding you correctly, you have multiple sheets that have specific Customer Journeys listed and then also a column that indicates the Impact Rating in text.

If so, you're on the right track with COUNTIFS! You will need one COUNTIFS formula per sheet, then add them all together for your total number.

For example:

=COUNTIFS({Customer Journey Column Sheet 1}, [Customer Journey]@row, {Impact Rating Sheet 1}, "High")

Then you can repeat this for Sheet 2 and add it together:

=COUNTIFS(sheet 1) + COUNTIFS(sheet 2) + COUNTIFS(sheet 3)

=COUNTIFS({Customer Journey Column Sheet 1}, [Customer Journey]@row, {Impact Rating Sheet 1}, "High") + COUNTIFS({Customer Journey Column Sheet 2}, [Customer Journey]@row, {Impact Rating Sheet 2}, "High") + COUNTIFS({Customer Journey Column Sheet 3}, [Customer Journey]@row, {Impact Rating Sheet 3}, "High")

Once you've built this out for "High", you can copy this same formula into the next column and adjust "High" to be "Medium" etc. Let me know if this makes sense and works for you.

Cheers,

Genevieve

EliseB

I've realised I probably need a kind of lookup and sum function, lookup the value in the customer journey column and then return the sum of the total high impacts in all sheets matching the high/customer journey arguments. Has anyone got any advice ?

Genevieve P.

Hi @EliseB

If I'm understanding you correctly, you have multiple sheets that have specific Customer Journeys listed and then also a column that indicates the Impact Rating in text.

If so, you're on the right track with COUNTIFS! You will need one COUNTIFS formula per sheet, then add them all together for your total number.

For example:

=COUNTIFS({Customer Journey Column Sheet 1}, [Customer Journey]@row, {Impact Rating Sheet 1}, "High")

Then you can repeat this for Sheet 2 and add it together:

=COUNTIFS(sheet 1) + COUNTIFS(sheet 2) + COUNTIFS(sheet 3)

=COUNTIFS({Customer Journey Column Sheet 1}, [Customer Journey]@row, {Impact Rating Sheet 1}, "High") + COUNTIFS({Customer Journey Column Sheet 2}, [Customer Journey]@row, {Impact Rating Sheet 2}, "High") + COUNTIFS({Customer Journey Column Sheet 3}, [Customer Journey]@row, {Impact Rating Sheet 3}, "High")

Once you've built this out for "High", you can copy this same formula into the next column and adjust "High" to be "Medium" etc. Let me know if this makes sense and works for you.

Cheers,

Genevieve

EliseB

Thanks Genevieve,

Due to the volume of reports and dashboards and the tight timeline we had I decided to go a different way and utilised pie charts and columns to split the data using the pre- existing reports with the view to refine this down the track when there is more time!

Thanks for these details I will save it and reference back when we want to revisit the option