Summary sum the issues by users column "Solved by" and sum the issues by users column "Solved by (b"

[alfonsoasenjo]

I hope someone can help with this query, I have a requirement for a query summary the total number of issue by the users in the column "Solved by" and the total number of issue by the users in the column "Solved by (before)" then get the % between them.

This is what I have:

ID	Solved by	Solved by (before)	Repeated
1	mario	ami	SAME ISSUE
2	mario	jack	SAME ISSUE
3	mario	tom	SAME ISSUE
4	jack	tom	SAME ISSUE
5	jack	tom	SAME ISSUE
6	tom	ami	DIFFERENT ISSUE
7	jack	mario	DIFFERENT ISSUE
8	peter	mario	DIFFERENT ISSUE
9	tom	jack	DIFFERENT ISSUE
10	jack	jack	DIFFERENT ISSUE
11	mario	mario	SAME ISSUE
12	tom	tom	SAME ISSUE
13	tom	tom	SAME ISSUE
14	jack	peter	DIFFERENT ISSUE
15	ami	peter	DIFFERENT ISSUE
16	ami	jack	SAME ISSUE
17	ami	mario	SAME ISSUE
18	peter	mario	SAME ISSUE
19	peter	peter	SAME ISSUE
20	peter	jack	DIFFERENT ISSUE
21	peter	tom	DIFFERENT ISSUE

This is what I want:

USER	Total ISSUE	Total SAME ISSUE	% SAME ISSUE	Total DIFFERENTE ISSUE	% DIFFERENT ISSUE
mario	4	3	75%	2	50%
jack	5	2	40%	3	60%
tom	4	5	125%	1	25%
peter	5	1	20%	2	40%
ami	3	0	0%	1	33%

I try get it by making measure:

Total ISSUE = COUNTROWS(DISTINCT(ID))--by "Solved by"
Total SAME ISSUE = COUNTROWS(DISTINCT(FILTER(Table1,Repeated="SAME ISSUE")))--by "Solved by (before)"
Total DIFFERENTE ISSUE = COUNTROWS(DISTINCT(FILTER(Table1,Repeated="DIFFERENTE ISSUE")))--by "Solved by (before)"
% SAME ISSUE = Total SAME ISSUE/Total ISSUE
% DIFFERENTE ISSUE = Total DIFFERENTE ISSUE/Total ISSUE

Then put it on a matrix, but it doesnt calculated by "solved by" and "solved by before".

Should I calculated it in a different table?

Any help is really appreciated. Thanks a lot

Maria J.

[alfonsoasenjo]

Any help is really appreciated. Thanks a lot

Maria J.

[v-deddai1-msft · Accepted solution]

Hi @alfonsoasenjo ,

You should unpivot columns to:

Then you can use the following measure:

Total ISSUE = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Sovled] = "Solved by")
Total SAME ISSUE = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Sovled] = "Solved by (before)",'Table'[Repeated] = "SAME ISSUE")
Total DIFFERENTE ISSUE = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Sovled] = "Solved by (before)",'Table'[Repeated]="DIFFERENT ISSUE")
% SAME ISSUE = [Total SAME ISSUE]/[Total ISSUE]
% DIFFERENTE ISSUE = [Total DIFFERENTE ISSUE]/[Total ISSUE]

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZDK-TzJRSdGtxUQAH...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai