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
sd_parekh
Helper I
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

sd_parekh_0-1676938320117.png

Image 1

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

 

 

sd_parekh_1-1676938726030.png

Image 2

Could you please help me to solve this problem??

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

Thanks

 

3 REPLIES 3
sd_parekh
Helper I
Helper I

Another Example ,

Database in Excel

sd_parekh_2-1676949294744.png

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.

 

 

sd_parekh_1-1676949239407.png

 

Also If I reset date filter for only 1 day.

Total( Column Subtotal)  Should be 1 for each student

sd_parekh_3-1676949516017.png

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

 

 

 

amitchandak
Super User
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.

Appreciate your Kudos.

Hi Amit, 

Thanks for quick reply. 

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

 

Measure = COUNTROWS(AttendanceDetails)

 

sd_parekh_0-1676948498040.png

 

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

 

sd_parekh_2-1676946112346.png

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. 

sd_parekh_3-1676946798185.png

IMAGE 3

sd_parekh_4-1676946839531.png

IMAGE 4

 

Let me know if you need more information. 

 

Thanks. 

 

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.