Calculation

[sks2701]

Hi- I have a data set from which I have to show in visuals the below calculation- can someone please help , how this can be achieved

Dropped Rate

Total count of dropped / Total count

Closed Rate

Total count of closed/ Total count

Approval Rate

Total count of In progress + Total count of under review/ total count

ID	Overall Status
1	In progress
2	In progress
3	In progress
4	Under review
5	In progress
6	In progress
7	In progress
8	Under review
9	In progress
10	In progress
11	In progress
12	In progress
13	In progress
14	Under review
15	In progress
16	In progress
17	In progress
18	In progress
19	In progress
20	Under review
21	Dropped
22	In progress
23	In progress
24	In progress
25	Closed
26	In progress
27	In progress
28	In progress
29	In progress
30	In progress
31	In progress
32	In progress
33	Under review
34	In progress
35	Under review
36	In progress
37	In progress
38	In progress
39	In progress
40	Under review
41	In progress
42	In progress
43	In progress
44	In progress
45	In progress
46	Under review
47	Under review
48	In progress
49	Closed
50	In progress
51	In progress
52	Under review
53	In progress
54	In progress
55	In progress
56	In progress
57	Under review
58	Under review
59	In progress
60	In progress
61	In progress
62	Under review
63	Under review
64	Under review
65	Under review
66	In progress
67	In progress
68	Under review
69	Under review
70	In progress
71	Closed
72	Under review
73	Under review
74	In progress
75	Under review
76	In progress
77	Dropped
78	In progress
79	In progress
80	Dropped
81	Closed
82	Under review
83	Under review
84	In progress
85	Under review
86	Under review

[v-robertq-msft · Accepted solution]

Hi, @sks2701 

According to your description, you can try these measures:

Dropped Rate =

var _tatalcount=COUNTX(ALL('Table'),'Table'[ID])

var _droppedcount=COUNTX(FILTER('Table',[Overall Status]="Dropped"),[ID])

return DIVIDE(_droppedcount,_tatalcount)

Closed Rate =

var _tatalcount=COUNTX(ALL('Table'),'Table'[ID])

var _closedcount=COUNTX(FILTER('Table',[Overall Status]="Closed"),[ID])

return DIVIDE(_closedcount,_tatalcount)

Approval Rate =

var _tatalcount=COUNTX(ALL('Table'),'Table'[ID])

var _approvalcount=COUNTX(FILTER('Table',[Overall Status]="In progress"||[Overall Status]="Under review"),[ID])

return DIVIDE(_approvalcount,_tatalcount)

You can set the measure type to “Percetage”, like this:

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[camargos88]

@sks2701 ,

_ApprovalRate = DIVIDE(CALCULATE(COUNTROWS('Table'), 'Table'[Overall Status] = "In progress") + CALCULATE(COUNTROWS('Table'), 'Table'[Overall Status] = "Under review"), CALCULATE(COUNTROWS('Table'),  ALL('Table'[Overall Status])), 0)

[sks2701]

thank you!

[v-robertq-msft]

Hi, @sks2701 

Have you solved your problem? Would you like to accept the reply as a solution if you find it's useful?

Thanks in advance!

Best Regards,

Community Support Team _Robert Qin

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

[camargos88]

@sks2701 ,

You can use these measures in a card:

_DroppedRate = DIVIDE(CALCULATE(COUNTROWS('Table'), 'Table'[Overall Status] = "Dropped"), CALCULATE(COUNTROWS('Table'),  ALL('Table'[Overall Status])), 0)

_ClosedRate = DIVIDE(CALCULATE(COUNTROWS('Table'), 'Table'[Overall Status] = "Closed"), CALCULATE(COUNTROWS('Table'),  ALL('Table'[Overall Status])), 0)

_ApprovalRate = DIVIDE(CALCULATE(COUNTROWS('Table'), 'Table'[Overall Status] = "In progress"), CALCULATE(COUNTROWS('Table'),  ALL('Table'[Overall Status])), 0)

[sks2701]

@camargos88 : thanks a ton this really helped , however for approved rate the formula would be 

(Total count of In progress + Total count of under review)/ total count

Could you help for this too- thank you again!