Forum Discussion

freemainia's avatar
freemainia
Helper I
3 years ago

Count values in one column that have consistent values in another column

I have a load of requirements that are prioritised by a number of people. I would like to know how many of the requirements are rated consistently, and how many are rated inconsistently.

 

For example, for the below table, this would be:

Rated consistently = 2

Rated inconsistently = 1

 

RaterRequirementRating
Luke1. I need Power BI to be awesomeHigh
Tim1. I need Power BI to be awesomeMedium
Paul1. I need Power BI to be awesomeHigh
Luke2. I need a mouseLow
Tim2. I need a mouseLow
Paul2. I need a mouseLow
Luke3. I need skillsMedium
Tim3. I need skillsMedium
Paul3. I need skillsMedium

 

 

Need Help

4 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi freemainia ,

     

    Create a calculated colum to pikcup any of the rating per requirement and then compare it against the rating in a row.  If the rating vs the random rating is the same, return 1 else 0.

    Rating Check =
VAR rating =
    CALCULATE (
        MAX ( 'Table'[Rating] ),
        FILTER ( 'Table', 'Table'[Requirement] = EARLIER ( 'Table'[Requirement] ) )
    )
RETURN
    IF ( 'Table'[Rating] = rating, 0, 1 )

    Create another calculated column to get the sum of Rating Check (calculcated column previous created) per requirement. If the sum if zero then constentent = yes else no

     

    Consistent? =
IF (
    CALCULATE (
        SUM ( 'Table'[Rating Check] ),
        ALLEXCEPT ( 'Table', 'Table'[Requirement] )
    ) > 0,
    "No",
    "Yes"
)

     

    Then in a visual, get the distinct count of requirement split between  whether it is consistent or not.

     

     

    Please see attached pbix for details

     

    • freemainia's avatar
      freemainia
      Helper I
      3 years ago

      Thanks danextian for your quick response.

      A few complicating things:

      - My Rating column doesn't actually have numbers in it (just the "I need X...")

      - Is it possible for the solution to created through measures? As, the count of consistent and inconsistent would ideally need to change based off the number of Raters selected.

      • danextian's avatar
        danextian
        Super User
        3 years ago

        Hello,

         

        Can you please post  a sample data that actually represents your current use case? Sometimes an overly simplied sample data results to an overly simplified solution 😊