Forum Discussion
Helper IIOverlay Visuals, combine non-related data into same chart?
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!
The "Line and stacked column" chart seems like it would serve your needs. You can use sales for column values, and scrap for line values I think.
Helper III 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.
- kwpbi
When you say you "would do a join" are you refering to "merge queries", or just creating a relationship?
Neither will work. The relationship would be many-to-many, which will result in loss of data if I use that to drive the visual.
I merged the tables as new and that's definitely not going to work. Sounds like it cannot be done without some higher level coding. Really seems like this should be simple, but Power BI cannot seem to figure out that I just want both data sets summed up by month. The same months. Oh well, thanks anyway. Maybe I'll try making a reference table with a bunch of nested if-statements.
- kwpbi
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!