cancel
Showing results for
Did you mean:
Helper I

## Matrix Column Subtotal Count Problem

Hi, I am trying to calculate Column Subtotal in matrix using measure.

Note:

1. All data resides in  AttendanceDetails table.

2. AttendanceStatusID is text field.

3. Some students have no AttendanceStatusID for some days.  Check First row of visual image 2.

I used following dax.

``````Measure = IF(
HASONEVALUE(AttendanceDetails[AttendanceDate]),
MIN(AttendanceDetails[AttendanceStatusId]),
COUNTROWS(AttendanceDetails)
)``````

If I select Date range from Date Range filter for one day, Matrix displays following result

Image 1

If changed day range from Date Range filter for multiple days matrix displays following result

Image 2

I need a Subtotal Count based on count of AttendanceStatusID irrespective of date Range days?

Thanks

3 REPLIES 3
Helper I

Another Example ,

Database in Excel

Student A has only attenadance For 01/05/2023. So In matrix Total(Column Subtotal) should be 1 not AttendanceStatusID. Similar case for Student D. Refer below image.

Also If I reset date filter for only 1 day.

Total( Column Subtotal)  Should be 1 for each student

Why Column Subtotal is wrong for single value for each student?

Super User

@sd_parekh , I think you should simply use

COUNTROWS(AttendanceDetails)

The information you have provided is not making the problem clear to me. Can you please explain with an example.

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Helper I

Hi Amit,

I did try to use countrows() function before and it gave me following result and it is correct.

``Measure = COUNTROWS(AttendanceDetails)``

IMAGE 1

But I want to display matrix based on AttendanceStatusID.

Each attendanceStatusId has Attendance Description such as Present, Absent,etc..

I have a unique symbol for each AttendanceStatusID . See IMAGE 2 .

I used following measure (Replaced AttendanceStatusID with Symbol field)

``````Attendance symbol Measure1 =
IF(
HASONEVALUE(AttendanceDetails[AttendanceDate]),
MIN(AttendanceDetails[Symbol]),
COUNTROWS(AttendanceDetails)
)
``````

IMAGE 2

Check First row and column Subtotal value, it should have 1 not AttendanceStatusID/Symbol in IMAGE 2.

The problem is that if we have single value across row it will display AttendanceStatusID/Symbol instead of rowcount value.

IMAGE 3

IMAGE 4

Thanks.