Forum Discussion

bhmiller89's avatar
bhmiller89
Icon for Helper V rankHelper V
8 years ago
Solved

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 p...
Need Help
erik_tarnvik's avatar
erik_tarnvik
Icon for Solution Specialist rankSolution Specialist
8 years ago

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))

 

bhmiller89's avatar
bhmiller89
Icon for Helper V rankHelper V
8 years ago

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

 

Job Number | Criteria 1| Critera 1...

  • BILASolution's avatar
    BILASolution
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    bhmiller89

     

    This way???

     

     

     

    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

    • bhmiller89's avatar
      bhmiller89
      Icon for Helper V rankHelper V
      8 years ago

      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"......

       

  • erik_tarnvik's avatar
    erik_tarnvik
    Icon for Solution Specialist rankSolution Specialist
    8 years ago
    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.