Forum Discussion

owenv's avatar
owenv
Frequent Visitor
4 years ago
Solved

% based on 2 columns

I have a table of vehicles that have regular inspections. The same vehicle will appear in the table more than once. Sometimes that vehicle will pass an inspection and soemtimes it will fail. I need to calculate the percentage of inspections that result in a pass. It seems like this should be easy but the measure I'm using always returns 100% when I drag the vehicle ID in. The % is correct when I use a basic card that aggregates across the entire table.

 

The (rough) DAX I'm using at the moment:

 

VAR numberOfInspections = COUNTX('inspections', 'inspections'[_index])

RETURN CALCULATE(numberOfInspections, 'inspections'[pass] = TRUE()) / numberOfInspections

This works on a card that doesn't include vehicle names. But if I add a column chart with vehicle IDs, it displays 100% for each vehicle. What am I missing?

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    Try these measures:

    Row count = COUNTROWS(Data)
    Pass count = CALCULATE([Row count],Data[pass]=TRUE())
    Pass % = DIVIDE([Pass count],[Row count])

    Hope this helps.

10 Replies

    • owenv's avatar
      owenv
      Frequent Visitor
      4 years ago

      Thanks for the input. Unfortunately it hasn't solved the situation

    • owenv's avatar
      owenv
      Frequent Visitor
      4 years ago

      The below is an anonymised example. I'd expect to see 50% returned for each vehicle with these values.

      vehiclepass
      vehicle 1TRUE
      vehicle 2FALSE
      vehicle 3TRUE
      vehicle 1FALSE
      vehicle 2TRUE
      vehicle 3FALSE

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

        Try these measures:

        Row count = COUNTROWS(Data)
        Pass count = CALCULATE([Row count],Data[pass]=TRUE())
        Pass % = DIVIDE([Pass count],[Row count])

        Hope this helps.

  • ribisht17's avatar
    ribisht17
    Super User
    4 years ago

    owenv 

    This should work >>>

     

    Step 1

    Total Vehicle = COUNTX(ALL(Vehicle), Vehicle[vehicle])
     
    Step 2
    Total Pass Vehicle = CALCULATE(count(Vehicle[Total Vehicle]),FILTER(all(Vehicle),Vehicle[pass]=true()))
     
    Step3
    Final % = [Total Pass Vehicle]/min(Vehicle[Total Vehicle])*100
     

     

    Regards,
    Ritesh
    Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
    My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users