Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |