How to drag values for matrix table correctly

[bernd0102]

Hello,

i have a problem in power bi. I want to create a matrix in power bi like the following:

Department	Target month	Nov 16	Dez 17	Jan 17	SUM actual	Deviation Actual/Target
A	20	23	18	17	58	-3,33 %
B	25	25	21	20	66	-12%

I choosed a matrix graph an dragged months as column, department as rows and manufactured goods as values. So far it worked, but I don’t know how to create the column “target month” in the matrix graph as well as the column Deviation “Actual/Target”.

Can someone tell me how to drag values so that I get the matrix chart above? I tested different variations but it didn’t worked. Do I need new measures?

My data in power bi  are as followed:

Table actual data:

Department	End of the month	Manufactured goods
A	30.11.2016	23
A	31.12.2016	18
A	31.01.2017	17
B	30.11.2016	25
B	31.12.2016	21
B	31.01.2017	20

Table target figures:

Department	End of the month	Target manufactured goods
A	30.11.2016	20
A	31.12.2016	20
A	31.01.2017	20
B	30.11.2016	25
B	31.12.2016	25
B	31.01.2017	25

I also have a date dimension table and these measures:

Deviation manufactured/target = ([SUM manufactured goods]-[SUM Target manufactured goods])/[SUM Target manufactured goods]

SUM manufactured goods = Sum([Manufactured goods])

SUM Target manufactured goods = SUM([Target manufactured goods])

Month target = TOTALMTD(SUM('target figures'[Target manufactured goods]);DateDimension[DateKey])

Month Manufactured goods = TOTALMTD(SUM(Actual[Manufactured goods]);DateDimension[DateKey])

Would be awesome if someone could help me to create a matrix/table lile I mentioned! I dont't know how to drag the data for it..

Thanks in advance!

Best Regards

[himanshu56 · Accepted solution]

Hi @bernd0102 , 

I cant help you completely here. But few things I have figured out:

1) For the TargetMonth, click the option dont summarize(Target Manufactured Goods)

2) For the calculation of deviation, you will have to make 2 measures in the Target Table

Measure = SUMX(Target,(SUM(Actual[Manufactured goods]) - SUM(Target[Target manufactured goods]))/COUNT(Target[End of the month]))

Measure 2 = [Measure] / SUM(Target[Target manufactured goods]). Also for measure 2 make sure under modelling tab, format it as percentage.

Attached are the snapshots.

Hope this helps!!

Thanks,

Himanshu

[bernd0102]

Hello @himanshu56

Thank you! It helped me a lot to see how to create the measures! Thanks for that. 🙂

But is there a possibility, that not the Total Amount of manufactured good are shown but the values for every month as a seperated column? That would be awesome...

Best Regards! 🙂

[himanshu56]

Hi @bernd0102,

Please create dax for all the months like shown below.

January = CALCULATE(TOTALMTD(SUM(Sheet2[Manufactured goods]),Sheet2[End of the month]),Sheet2[Month Name] = "January" )

Similarly create dax for November and December also.

For month name, you will need to parse 'End of month' to month Name. Also make sure data type of 'End of month' is Date.

I have attached the snapshots for your reference.

 I hope this answers all your queries.

Thanks,

Himanshu