cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
uk141
Helper III
Helper III

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
v-jayw-msft
Community Support
Community Support

Hi @uk141 ,

 

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, @uk141 

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 accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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)

v-jayw-msft
Community Support
Community Support

Hi @uk141 ,

 

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

amitchandak
Super User
Super User

@uk141 , 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)))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors