how to put to matrix the performance change of operators between 2 period

[szysz]

I have a dataset likes this:

DataSet
Period	PeriodYear	PeriodValue	Login	Performance	Efficiency
Quarter	2023	1	LoginA	A	120
Quarter	2023	2	LoginA	B	100
Quarter	2023	3	LoginA	C	78
Quarter	2023	1	LoginB	C	96
Quarter	2023	2	LoginB	A+	103
Quarter	2023	3	LoginB	A	125
Quarter	2023	1	LoginC	B	110
Quarter	2023	1	LoginD	A	123
Quarter	2023	2	LoginD	C	108
Quarter	2023	3	LoginD	A	135
Quarter	2023	1	LoginE	B+	115
Quarter	2023	2	LoginE	C	108
Quarter	2023	3	LoginE	A	135
Quarter	2023	2	LoginF	F	108

Filter is for the the Period columns, and for example we would like to compare 2023 first quarter to 2023 second quarter.

I need to create visual like these:

This is working in excel as we can vlookup to Q1 data the Q2 data. But in PowerBi I can use only measures, and even if I solve to put the measuers to columns, I can not find the right formula to find the count of Left operators ( they don't have any line in Q2).

The second problem that for Visual2 we need at first the efficiency difference from the two period and then average them.

Please see on the attached link in excel the whole calculation.

https://docs.google.com/spreadsheets/d/1GS_AAU3EFFv73gUi0yhlpv-3F5GYY_Dr/edit#gid=557262223 

[szysz · Accepted solution]

Hi,

I could solved it. Please see on the following link my solution:

https://drive.google.com/file/d/1xLssjKofT0IVitItiQbo_dqJ_iw-swZ5/view?usp=drive_link

[Anonymous]

Hi @szysz ,

It's glad to hear that your problem has been resolved. And thanks for sharing your solution here, it will help the others in the community find the solution easily if they face the same problem as yours.  Thank you.

Best Regards

[Anonymous]

Hi @szysz ,

If I understand you correctly, you are trying to dynamically compare the number of logins and the average of efficiency over two different periods based on the options selected by lookupname, right? If this is the case, I have analyzed the data you have provided and the results of the visuals you are trying to achieve.  I tried everything possible, but failed...

However, I have found a possible workaround that might help you achieve a similar result. I hope this can be a suitable alternative for you. I created a sample pbix file(see the attachment), please find the details in it.

If you have any questions or concerns, please let me know. I am happy to assist you further. Thank you for your understanding.

Best Regards

[szysz]

Hi @@v-yiruan-msft,

Thank you, but this is not what management want.

And actually I'm pretty close to solve it. the Logins part is ready with counting Left and New ones in the matrix. Currently I'm finilizing the measures for the efficiency matrix. I will post my soluion as soon as ready.