Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
@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)
thank you!
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.
@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)
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |