Forum Discussion
Making a visual that pulls data from two tables
Hi, I want to use a visual to show the projected and actual sales of a product by quarter such as in Table C. My data sources would be Table A and B. I am new to powerBI and would be really grateful if anyone is able to help. Thank you.
| Table A | ||||
| Product | Product ID | Sales Date | Stock Date | Category |
| A | 12689 | 9/5/2020 | 4/5/2020 | 1 |
| B | 78563 | 6/5/2020 | 5/29/2020 | 2 |
| D | 87654 | 1/24/2020 | 4/22/2020 | 4 |
| C | 76878 | 3/21/2020 | 3/25/2020 | 3 |
| C | 76878 | 6/21/2020 | 9/12/2020 | 3 |
| D | 87654 | 2/5/2020 | 2/24/2020 | 4 |
| A | 12689 | 1/15/2020 | 2/23/2020 | 1 |
| A | 12689 | 12/7/2020 | 12/9/2020 | 1 |
| B | 78563 | 9/29/2020 | 4/14/2020 | 2 |
| C | 76878 | 10/2/2020 | 7/12/2020 | 3 |
| C | 76878 | 6/25/2020 | 2/25/2020 | 3 |
| D | 87654 | 4/3/2020 | 9/9/2020 | 4 |
| D | 87654 | 11/25/2020 | 6/7/2020 | 4 |
| C | 76878 | 2/24/2020 | 2/19/2020 | 3 |
| A | 12689 | 4/5/2020 | 6/9/2020 | 1 |
| B | 78563 | 2/26/2020 | 12/17/2020 | 2 |
| B | 78563 | 3/16/2020 | 8/29/2020 | 2 |
| . | . | . | . | . |
| . | . | . | . | . |
| . | . | . | . | . |
| C | 76878 | 4/22/2020 | 10/12/2020 | 3 |
| Table B | ||
| Product | Quarter | Projected |
| A | Q1 | 10 |
| A | Q2 | 20 |
| A | Q3 | 10 |
| A | Q4 | 24 |
| B | Q1 | 34 |
| B | Q2 | 20 |
| B | Q3 | 10 |
| B | Q4 | 20 |
| C | Q1 | 23 |
| C | Q2 | 5 |
| C | Q3 | 15 |
| C | Q4 | 18 |
| D | Q1 | 17 |
| D | Q2 | 15 |
| D | Q3 | 30 |
| D | Q4 | 25 |
Output
| Table C | |||
| Product | Quarter | Projected | Actual |
| A | Q1 | 10 | 10 |
| A | Q2 | 20 | 25 |
| A | Q3 | 10 | 12 |
| A | Q4 | 24 | 24 |
| B | Q1 | 34 | 34 |
| B | Q2 | 20 | 21 |
| B | Q3 | 10 | 17 |
| B | Q4 | 20 | 18 |
| C | Q1 | 23 | 24 |
| C | Q2 | 5 | 6 |
| C | Q3 | 15 | 7 |
| C | Q4 | 18 | 23 |
| D | Q1 | 17 | 16 |
| D | Q2 | 15 | 13 |
| D | Q3 | 30 | 35 |
| D | Q4 | 25 | 26 |
Hi Anonymous
Try this, use the first three columns in tableB
Then create this measure
Actual = CALCULATE ( COUNTROWS ( 'Table A' ), FILTER ( ALL ( 'Table A' ), 'Table A'[Product] = MIN ( 'Table B'[Product] ) && QUARTER ( 'Table A'[Sales Date] ) = VALUE ( RIGHT ( MIN ( 'Table B'[Quarter] ), 1 ) ) ) )Result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous , In table one you can create Qtr based on any of the date
example
c
Then you can create a combine key and join both tables
Key = [Product] & "-" [QTR]
you can not get data in visual together
do in Power Query , new columns
="Q"& Number.ToText(Date.QuarterOfYear([sales Date]))
In both tables
"Q"& Number.ToText(Date.QuarterOfYear([sales Date]))
merge two tables
Append and merge : https://radacad.com/append-vs-merge-in-power-bi-and-power-queryHi Anonymous
Try this, use the first three columns in tableB
Then create this measure
Actual = CALCULATE ( COUNTROWS ( 'Table A' ), FILTER ( ALL ( 'Table A' ), 'Table A'[Product] = MIN ( 'Table B'[Product] ) && QUARTER ( 'Table A'[Sales Date] ) = VALUE ( RIGHT ( MIN ( 'Table B'[Quarter] ), 1 ) ) ) )Result
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
