Forum Discussion

stcrazy123's avatar
stcrazy123
New Member
7 years ago
Solved

Counting values in a calculated column, sliders applied

Help!

 

I am in a school, and collecting data on student performance (a 7 to1 scale) I have a benchmark score (also 7 to 1 scale)

 

I have calculated a difference using a calculated column.

 

I would now like to count the number of students who are below benchmark (ie <0) at benchmark (0) and above (>0)

 

Yet when i use the calculated column i have 2 values for each student, whereas it is the sum of these vales that i need to count.

 

eg student A scored 4, benchmark 5. therefore is -1 against bechmark.

 

Using the calculated column tries to list the vales of 4 and -5 not the sum of -1. hence any counting I am doing is doubling the items

 

I hope this makes sense, How do i do this?

 

Thanks for any help in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi stcrazy123,

     

    You can add a calculated table to store category types, then write a measure to calculate count of correspond type.

     

    Table formula:

    Category =
SELECTCOLUMNS (
    { "less than zero", "zero", "greater than zero" },
    "Type", [Value]
)

    Measure:

    Measure =
VAR currCategory =
    SELECTEDVALUE ( Category[Type] )
VAR temp =
    ADDCOLUMNS (
        ALLSELECTED ( Table ),
        "Oct v Cat", [Oct] - [Cat],
        "Category", IF ([Oct] - [Cat] > 0,
            "greater than zero",
            IF ( [Oct] - [Cat] < 0, "less than zero", "zero" )
        )
    )
RETURN
    COUNTROWS ( FILTER ( temp, [Category] = currCategory ) )

     

    Then you can create a table visual with 'category[type]' column and above measure.

     

    If above not help, please share some sample data or pbix file with expected result for test and coding formula.

     

    Regards,

    Xiaoxin Sheng

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI stcrazy123,


    Can you please share some sample data to help us clearly your data structure and coding formula?

     

    In addition, did you mean create a dynamic calculated column based on slicer? If this is a case, current power bi not support this, please use measure formula to instead.

     

    Regards,

    Xiaoxin Sheng

    • stcrazy123's avatar
      stcrazy123
      New Member
      7 years ago

      Hi
       
      thank you for your reply.
       
      Yes it would be dynamic. In that contextually, if I pick Biology as a subject as a slicer, and year 12 as a slicer, I would want to see all students in year 11 who take biology.
       
      From the calculated column, which shows the SUM of "Oct Grade" MINUS "CAT X", I would like to count the number less than zero, the number at zero, and the number greater than zero. in the column "Oct v CAT X"
       
      It can just be a measure (or three)
       
      essentially if it was in excel I would just do a countif formula, on the calculated column.

       

      Thanks for your help

      note the table to the right is the same data listed as individual entries, whereas the table to the left is the sum of those entries per student

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi stcrazy123,

         

        You can add a calculated table to store category types, then write a measure to calculate count of correspond type.

         

        Table formula:

        Category =
SELECTCOLUMNS (
    { "less than zero", "zero", "greater than zero" },
    "Type", [Value]
)

        Measure:

        Measure =
VAR currCategory =
    SELECTEDVALUE ( Category[Type] )
VAR temp =
    ADDCOLUMNS (
        ALLSELECTED ( Table ),
        "Oct v Cat", [Oct] - [Cat],
        "Category", IF ([Oct] - [Cat] > 0,
            "greater than zero",
            IF ( [Oct] - [Cat] < 0, "less than zero", "zero" )
        )
    )
RETURN
    COUNTROWS ( FILTER ( temp, [Category] = currCategory ) )

         

        Then you can create a table visual with 'category[type]' column and above measure.

         

        If above not help, please share some sample data or pbix file with expected result for test and coding formula.

         

        Regards,

        Xiaoxin Sheng