Get percentage based on three columns/ three different values

[Anonymous]

Good Afternoon,

I am trying to divide Rating/Question (In this case, PRSQ5000) by the total count of 4006.

Manually I was been able to, but since I need to use an slicer the percentage need to update accordingly.

I tried converting the 84 to Show as Value: Percentage of Grand Total and the result I get is always 100%. Below picture for reference of the fields and applied filters:

I did the following (Sum of Rating of 1 and question: PRSQ5000 then divided by the grand total of 4009) but it did not work as expected even tho I got the expected percentage:

I would greatly apprecite if someone with knowledge could contact me for more details.

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description,  you can put your field in a Matrix visual and then write a measure to implement it according to your needs.

For your question,I don't have a clear idea which field your numerator and denominator come from, and how [Rating] is grouped,can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

[Anonymous]

Good morning, 

Thanks! I will try the matrix. Providing additional information below;

File download: https://anonfiles.com/udJ5jfA3y9/KPI_s_Draft_pbix

Numerator: column.QUESTION and column.RATING

Denominator: column.KPI_SURVEY_ID (This is the total count to be divided by)

The outputs should be getting the percentage of each rating under the specific question. The image I uploaded can be used as reference, I will include on this reply a whole screenshot of the full page.

MY SCREENSHOT:

The percentages I got are put manually, this cause the slicer to not work.

WHAT I AM TRYING TO REPLICATE WITH UPDATED DATA:

SAMPLE DATA;

KPI_SURVEY_ID|	QUESTION|	RATING
22	PRSQ5001	5
23	PRSQ5002	5
24	PRSQ5003
25	PRSQ5000	5
26	PRSQ5001	1
27	PRSQ5002	1
28	PRSQ5003
29	PRSQ5000	1
30	PRSQ5001	2
31	PRSQ5002	3
32	PRSQ5003
33	PRSQ5000	5
34	PRSQ5001	1
35	PRSQ5002	5
36	PRSQ5003
37	PRSQ5000	2
38	PRSQ5001	4
39	PRSQ5002	4
40	PRSQ5003
41	PRSQ5000	5
42	PRSQ5001	1
43	PRSQ5002	1
44	PRSQ5003
45	PRSQ5000	3
46	PRSQ5001	4
47	PRSQ5002	4
48	PRSQ5003
49	PRSQ5003
50	PRSQ5000	3
51	PRSQ5001	4
52	PRSQ5002	5
53	PRSQ5003
54	PRSQ5000	4
55	PRSQ5001	1
56	PRSQ5002	5
57	PRSQ5003
58	PRSQ5000	1
59	PRSQ5001	2
60	PRSQ5002	2
61	PRSQ5000	5
62	PRSQ5001	1
63	PRSQ5002	5
64	PRSQ5003
65	PRSQ5000	5
66	PRSQ5001	1
67	PRSQ5002	1
68	PRSQ5003
69	PRSQ5000	4
70	PRSQ5001	1
71	PRSQ5002	5
72	PRSQ5003
73	PRSQ5000	5
74	PRSQ5001	5
75	PRSQ5002	5
76	PRSQ5003
77	PRSQ5000	4
78	PRSQ5000	4
79	PRSQ5001	1
80	PRSQ5001	1
81	PRSQ5002	1
82	PRSQ5002	1
83	PRSQ5003
84	PRSQ5003
85	PRSQ5000	4
86	PRSQ5001	1
87	PRSQ5002	1
88	PRSQ5003
89	PRSQ5000	4
90	PRSQ5001	1
91	PRSQ5002	1
92	PRSQ5003
93	PRSQ5000	5
94	PRSQ5001	1
95	PRSQ5002	5
96	PRSQ5003
97	PRSQ5000	3
98	PRSQ5001	4
99	PRSQ5002	4

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

Here are the steps you can refer to :

(1)This is my test data which is the same as yours:

(2)We can create two measures :

Response = IF( HASONEVALUE('Sheet3'[RATING]), COUNT('Sheet3'[QUESTION|]) , FORMAT( DIVIDE( SUM('Sheet3'[RATING]) ,  COUNT('Sheet3'[RATING])),"0.00")  )

% = DIVIDE([Response], CALCULATE( COUNT('Sheet3'[KPI_SURVEY_ID|]), ALLSELECTED('Sheet3'[RATING]) ))

(3)Then we can put the measure and the filed we need in the Matrix visual and we will meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

[Anonymous]

Good Morning!

Thanks for your reply! This mostly answers my question! I have two issues; 1. The values that are suppose to shown as percentage are shown as whole numbers 2. How may I "convert" this Matriz to the visual layout I am trying to replicate ? Is there a way to use the values of the matrix as reference for another visual ?

Picture below of the layout I am trying to replicate:

FILE DOWNLOAD: https://1drv.ms/u/s!AhTlVF_V8uDigYBNuw4VvHH2L0KTJg?e=NEv9m6

EDIT: After verifying the solution you provided resolves my question! Thanks!

[v-yueyunzh-msft]

Hi, @Anonymous 

The .pbix you provide i cannot open it , and i will research your problem through the introduction you provide.Can you share the .pbix to us just with oneDrive link ? And can you provide a sample output table with your sample data you provided.

Best Regards,

Aniya Zhang

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