Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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 studentnote 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

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.