Forum Discussion
PowerBI Matrix
I'm new in power bi and in need of help on how I can do this in matrix. I need a produce these on thousands of data but in simple terms my table looks like the following
Table1
| Date | Product | Sales |
| 01/01/2019 | Soap | 100 |
| 01/05/2019 | Shampoo | 50 |
| 01/07/2019 | Shampoo | 150 |
| 01/20/2019 | Soap | 250 |
| 01/22/2019 | Soap | 300 |
Table2
| Product | Month | Quota |
| Soap | January | 200 |
| Soap | February | 300 |
| Shampoo | January | 50 |
| Shampoo | February | 100 |
I need a matrix that looks like this
| x | Jan | Jan | Feb | Feb | Total | Total |
| x | Quota | Actual | Quota | Actual | Quota | Actual |
| Shampoo | 50 | 200 | 100 | 0 | 150 | 200 |
| Soap | 200 | 650 | 300 | 0 | 500 | 650 |
| Total | 250 | 850 | 400 | 0 | 650 | 850 |
I hope someone can help, thanks.
Hi shalnark0727,
Create a calendar table and Products table and make a one to many relationship between these tables and the other two, then just create the following to measure to use on your matrix:
Quota Total = SUM(Table2[Quota]) Sales Total = IF(SUM(Table2[Quota]) = BLANK();BLANK(); SUM(Table1[Sales]) + 0)
Check PBIX file attach.
Regards,
MFelix
2 Replies
Hi shalnark0727,
Create a calendar table and Products table and make a one to many relationship between these tables and the other two, then just create the following to measure to use on your matrix:
Quota Total = SUM(Table2[Quota]) Sales Total = IF(SUM(Table2[Quota]) = BLANK();BLANK(); SUM(Table1[Sales]) + 0)
Check PBIX file attach.
Regards,
MFelix
Hi shalnark0727,
Create a calendar table and Products table and make a one to many relationship between these tables and the other two, then just create the following to measure to use on your matrix:
Quota Total = SUM(Table2[Quota]) Sales Total = IF(SUM(Table2[Quota]) = BLANK();BLANK(); SUM(Table1[Sales]) + 0)
Check PBIX file attach.
Regards,
MFelix