Forum Discussion
Helper IIOverlay Visuals, combine non-related data into same chart?
Hi,
I have two tables of data (table 1: scrap costs, and table 2: total sales) that are not related and come from two different systems. Each table consists of hundreds of entries with a $ amount and a date stamp, either for a scrap entry or an invoice (amongst other bits, but this is all we care about here). For each table, I can easily make a nice chart that groups sums by month, so that my x-axis has JAN, FEB, MAR, etc., and my y-axis has the total $ amount for that month.
What I would like to do, if possible, is add a line graph of the scrap cost data to my existing bar graph for sales data. Also, because the sales #'s will be so much larger than the scrap #'s for each month, it would be nice to be able to scale the y-axis for the scrap #'s only, and include data labels. If successful, it would look something like the attached (this is just some quick and dirty MS Paint edits added to my existing scrap cost chart).
Thanks in advance for your help!
The reference table built with nested if statements worked. I created a new column in each table, assigned a number code for the month of each row using nested if statements, created a new table from each of those tables that summed up the scrap/sales based on the month code, created a 1:1 relationship between those two tables based on month code columns, and then finally created my reference table that contains month number, total sales for that month, and total scrap for that month. Voila! Silly Power BI. Shouldn't have been that difficult to get this result, in my opinion. Here's the final (and beautiful, might I add) result.
Thanks for helping me figure it out!
6 Replies
kwpbiI tried this, but it doesn't want to put the data together. See my uploads. When I put the "INVOICEDATE" data in the "Shared axis" field before adding the "Closed Date" data, my "Sum of Scrap Cost" data becomes one static total value for every month. If I reverse the order, my "INVOICEAMOUNT" data becomes one static value.
I'm not sure how to get both of them to report correct monthly sums at the same time.
The date columns from both tables are formatted the same: *3/14/2001 (M/d/yyyy).
Sales totals are good, scrap line is notScrap line is good, sales totals are not
My underlying assumption is that it can't deal with the data sets not being connected. Since the tables are not-related, I think you should probably relate them and the problem goes away. Since date is the common link between the two $ columns, I would do a join of the 2 tables and then render from there.
