Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
---|---|
110 | |
96 | |
76 | |
63 | |
55 |
User | Count |
---|---|
142 | |
107 | |
89 | |
84 | |
65 |