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

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? 

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

    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.

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

    bhmiller89

     

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

     

    Criteria

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      the link is not working can you attach the solution ?