Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Percentage of the selected sum by condition.

There is a table that has values like below.
There are lines that say "Not OK". The number of these lines is 100%
How can I make a graph and a filter so that when I select a specific problem, I see how many percent of such lines are from the total number of lines, where are they in the Not OK status?
I tried the "Percentage of Grand Total" option in the values menu, but obviously it doesn't show what I want.

 

idStatusProblem
1OK 
2Not OK1
3OK 
4OK 
5OK 
6Not OK2
7OK 
8OK 
9OK 
10Not OK3
11Not OK1
12Not OK2
13OK 
14OK 
15OK 
16OK 
17OK 
18Not OK4
19Not OK4
20OK 
21Not OK2
22Not OK3
23OK 
24OK 
25OK 
1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Will "sum (number of Not OK)" be filtered by month? If it will ,try the first measure, if not, try second.

measure1 = 
var select_count=calculate(count('table'[id]),filter(all('table'),'table'[month]=selectedvalue('table'[month])&&'table'[problem]=selectedvalue('table'[problem])&&'table'[status]="Not OK"))
var all_count=calculate(count('table'[id]),filter(all('table'),'table'[month]=selectedvalue('table'[month])&&'table'[status]="Not OK"))
return
select_count/all_count

measure2 = 
var select_count=calculate(count('table'[id]),filter(all('table'),'table'[problem]=selectedvalue('table'[problem])&&'table'[status]="Not OK"))
var all_count=calculate(count('table'[id]),filter(all('table'),'table'[status]="Not OK"))
return
select_count/all_count

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below whether it is what you are looking for.

All measures are in the sample pbix file.

Picture3.png

 

https://www.dropbox.com/s/qmcdq55u87n3o9u/uk141.pbix?dl=0 

 

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

Not. The axis should be the month, which I did not show in the example.
To the left of the visualization there should be a Slicer, which allows you to select the desired problem and see on the graph the number of lines with this problem / sum (number of Not OK)

Hi @Anonymous ,

 

Will "sum (number of Not OK)" be filtered by month? If it will ,try the first measure, if not, try second.

measure1 = 
var select_count=calculate(count('table'[id]),filter(all('table'),'table'[month]=selectedvalue('table'[month])&&'table'[problem]=selectedvalue('table'[problem])&&'table'[status]="Not OK"))
var all_count=calculate(count('table'[id]),filter(all('table'),'table'[month]=selectedvalue('table'[month])&&'table'[status]="Not OK"))
return
select_count/all_count

measure2 = 
var select_count=calculate(count('table'[id]),filter(all('table'),'table'[problem]=selectedvalue('table'[problem])&&'table'[status]="Not OK"))
var all_count=calculate(count('table'[id]),filter(all('table'),'table'[status]="Not OK"))
return
select_count/all_count

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@Anonymous , Try one of the two measure

 


divide(calculate(count(Table[ID]), filter(Table, search("Not OK",Table[Status],,0)>0)), calculate(count(Table[ID])))

 

 

divide(calculate(count(Table[ID]), filter(Table, search("Not OK",Table[Status],,0)>0)), calculate(count(Table[ID]), allselected(Table)))

Anonymous
Not applicable

Both are not. I need a Slicer on the side, which would allow me to show only a certain problem, but to see not just the quantity, but the quantity with this problem / the quantity Not OK of everything.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.