Forum Discussion
Splice data from 2 tables
Hi there!
I have 2 tables that have a quantity and a cost. Ideally, they would be in one table, but the calculations are too complex to have them combined.
In this simplified example, I have 2 tables,
| Table Cats | ||
| Date | Cat Amount | Cat Cost |
| 6/30/2018 | 4 | 32 |
| 7/1/2018 | 4 | 34 |
| 7/2/2018 | 4 | 44 |
| 7/3/2018 | 3 | 22 |
| 7/4/2018 | 6 | 35 |
| 7/5/2018 | 2 | 21 |
| 7/6/2018 | 4 | 45 |
| Table Dogs | ||
| Date | Dog Amount | Dog Cost |
| 6/30/2018 | 3 | 987 |
| 7/1/2018 | 4 | 76 |
| 7/2/2018 | 5 | 78 |
| 7/3/2018 | 4 | 77 |
| 7/4/2018 | 16 | 788 |
| 7/5/2018 | 8 | 776 |
| 7/6/2018 | 4 | 88 |
I would like to have one bar chart showing sales per day. The result would be spliced by either cat OR dog, depending what was chosen on the splicer. How can I achieve this with a dax measure? Note, I cannot merge queries.
Any help is much appreciated!
Cheers,
Corey
3 Replies
Hi Anonymous
Please look at my test, steps are below
In query editor
1.Merge queries as new
2.in the Merge 1 table, expand columns from table Dogs
3. add two custom columns to calculate sales for Dogs and Cats
4.select the two custom columns "Sales for Cat" and "Sales for Dog", then select "unpivot only selected columns"
5.add a conditional column to define which categoary each value belong to, Dog or Cat.
6.add columns in the chart
Best Regards
Maggie
