cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stcrazy123
New Member

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Counting values in a calculated column, sliders applied

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

 

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

3 REPLIES 3
Community Support
Community Support

Re: Counting values in a calculated column, sliders applied

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
stcrazy123
New Member

Re: Counting values in a calculated column, sliders applied

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

Screen Shot 2018-11-13 at 8.42.19 AM.pngnote 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

Community Support
Community Support

Re: Counting values in a calculated column, sliders applied

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

 

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors