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
bhmiller89
Helper V
Helper V

Calculate Percentage value based on Slicer Selection

I have a list of Job Numbers and columns that determine whether a job meets any of 5 criteria (let's call them Criteria1-5)

 

I need to create a pie chart or even just a data card that shows what percentage of Job Numbers meet the selected criteria.

 

So, if I have 3 out of the 5 criteria slicers selected, what percentage of the total count of existing jobs meet this?

 

Example: there are 282 total jobs and 2 of them meet all 5 criteria. So when I have all 5 criteria slicers set to "Yes", I need something that shows me a value of "0.71%" and so on. 

1 ACCEPTED SOLUTION

@bhmiller89

 

I have an idea... I hope this helps, press the next link

 

Criteria

View solution in original post

12 REPLIES 12
BILASolution
Solution Specialist
Solution Specialist

Hi @bhmiller89

 

To solve your answer...

 

1. Data Sample (In my case, the table is called "Table 1")

 

data.png

 

2. Create the next measures...

 

Total Jobs = CALCULATE(DISTINCTCOUNT(Table1[Job Number]);ALL(Table1)) 
Total Criteria = DISTINCTCOUNT(Table1[Job Number])
Percentage = DIVIDE([Total Criteria];[Total Jobs]) 

 

3. The results look like...

 

result.png

 

 

I hope this helps

 

Regards

BILASolution

erik_tarnvik
Solution Specialist
Solution Specialist

Assuming you have the criteria in a table called Criteria (I have called them "A"-"E") that you use for the slicer, and assuming the jobs are ina table called Jobs with criteria Columns containing boolean values (TRUE or FALSE) named A through E, the below should do it. The Criteria table can have no relationship with the Jobs table, and if you have other slicers involved it could require changes to the formula.

 

 

Percentage = COUNTROWS(FILTER(Jobs, IF(CONTAINS(Criteria,Criteria[Criteria],"A"), Jobs[A], TRUE) &&
	                           IF(CONTAINS(Criteria,Criteria[Criteria],"B"), Jobs[B], TRUE) &&
	                           IF(CONTAINS(Criteria,Criteria[Criteria],"C"), Jobs[C], TRUE) &&
	                           IF(CONTAINS(Criteria,Criteria[Criteria],"D"), Jobs[D], TRUE) &&
	                           IF(CONTAINS(Criteria,Criteria[Criteria],"E"), Jobs[E], TRUE)))/COUNTROWS(ALL(Jobs))

 

@erik_tarnvik

 

I think I know the bigger issue.

 

the criteria are stored in two different tables based on where each data point is kept.

 

I  think I need to create a calculated table to pull in the JOb numbers and each column with criteria. I've never done that before. 

 

If that's the case, could you provide me some guidance? 

@bhmiller89

 

I have an idea... I hope this helps, press the next link

 

Criteria

Anonymous
Not applicable

the link is not working can you attach the solution ?

The solution link wont open and


@BILASolution wrote:

@bhmiller89

 

I have an idea... I hope this helps, press the next link

 

Criteria



I have similar issue

Hi @bhmiller89,

I think you have mixed up the Criteria and the ActiveProject tables in your formula. The Criteria table needs to be separate from the ActiveProjects table and with no relationship with your ActiveProjects table, and is used for the slicer. Otherwise this wont work. With regards to some of the criteria being in different tables, the easiest way to resolve that is to create calculated columns that "pull in" the missing citeria to the ActiveProjects table.

@erik_tarnvik

 

I used your calculations: 

 

Test%Total = COUNTROWS(FILTER(ActiveProjects, IF(CONTAINS(ActiveProjects, 'dpmgr vwJMWebJobSummary'[IsSOWHrs>300], "Yes"), ActiveProjects[Job Number], TRUE())&& IF(CONTAINS(ActiveProjects, 'dpmgr vwJMWebJobSummary'[IsLengthDays>180], "Yes"), ActiveProjects[Job Number], TRUE()) && IF(CONTAINS(ActiveProjects, 'dpmgr vwJMWebJobSummary'[IsActualEHR < $160], "Yes"), ActiveProjects[Job Number], TRUE()) && IF(CONTAINS(ActiveProjects, ActiveProjects[Is%HrsCompletion>75%], "Yes"), ActiveProjects[Job Number], TRUE()) && IF(CONTAINS(ActiveProjects, ActiveProjects[IsLastJR>60], "Yes"), ActiveProjects[Job Number], TRUE())))/COUNTROWS(ALL(ActiveProjects))

 

but I'm getting an error  "Calculation error in measure.... Cannot convert value "2014-DLL-J27 (which is a job number), of type Text to type True/False" 

I have them as columns in the Jobs Table as "Yes"/"No" Values 

 

Job Number | Criteria 1| Critera 1...

If you have them as “Yes”/“No”, just change Jobs[A] to Jobs[A] = “Yes” and so on for each criteria in the measure I proposed.

@bhmiller89

 

This way???

 

result.png

 

 

Total Jobs = CALCULATE(DISTINCTCOUNT(Job[Job Number]);ALL(Job)) 
Total Criteria = CALCULATE(DISTINCTCOUNT(Job[Job Number]);Job[Value] = "Yes") 
Precentage = DIVIDE([Total Criteria];[Total Jobs]) 

Best Regards

BILAsolution

@BILASolution

 

 

not quite, I have columns written for each. Example: the column is titled "Criteria 1" and the values are either "Yes" or "No" based on an If/Then statement. I did it this way for each Criteria.  

 

Job Number| Criteria 1| Criteria 2|Criteria 3| .......

_____________________________________________________

XXX-XXX| "Yes"| "No"| "Yes"......

 

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.