PowerBI Matrix

[shalnark0727]

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.    

[MFelix · Accepted 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

[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