Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |