Need help creating a chart using two different reports.

[romannick1]

Hi all,

I am trying to create a visualization that compares two different sets of data. The first report is a FY19 report showing employees actual hours charged by week. The second report shows budgeted employee hours by week for FY20. Essentially I am trying to create a line/ bar graph that shows over a fiscal year where there are deviations between prior year actual hours/ current year budgeted hours to identify where there is overstaffing or deficiences. 

I know I can create pivots to sum the hours by week and compare or generate variances in a chart but looking to create a visual more management presentation purposes.

Anybody have any idea if this is doable? Appreciate any help, thanks!

[romannick1]

Thanks for explaining. I'll play around with it but your explanations have given me some ideas!

[Aron_Moore]

Sure it's doable. Depending on how your data is coming in you may need to create some custom columns. For example if I had this data (just 10 weeks for demonstration):

I could put the Year column as the legend to differentiate between the series.

[romannick1]

Thanks for the quick response! Does the data need to be aggregated into one excel file or table? The problem I think I'm also going to run into is that the FY19 actual hours has charged time in 2020 and that the 2020 budget will project hours into the first month of 2021. I tried to attach some photos so you can see the date format. Would you have suggestions for the custom columns? Does a relationship need to be established to make a chart that displays both sets of data?

2019 Actuals2020 Budget

[Aron_Moore]

Like I said it depends on how your data is coming in. My example was from one table (the easiest way).

If you have multiple tables you could append them, join them with some sort of bridging table, or build measures that calculate based on values in other tables. There is a lot of flexibility so the method could determine how you do it.