cancel
Showing results for
Did you mean: Regular Visitor

## How to show the count of rows in Powerbi matrix visual based on Quarter wise cumulative way

Hi Experts,
I need some help on this counting rows in my data, here is the sample data which i am using to build a report.

 User Course_Date(mm/dd/yyyy) Quarter Year abc 7/16/2020 Qtr3 2020 def 12/24/2020 Qtr4 2020 ghi 1/1/2021 Qtr1 2021 jkl 4/1/2021 Qtr2 2021 mno 7/9/2021 Qtr3 2021 adg 11/5/2021 Qtr4 2021 gjm 2/4/2022 Qtr1 2022 hhh 2/5/2022 Qtr1 2022 iii 7/7/2022 Qtr3 2022

I want to show the count data in Matrix visual as below. Simple logic = Count rows till that particular Quarter from starting.

1 ACCEPTED SOLUTION  Super User

Here is one way. First create a date table for the model (recommended best practice). You can use the following DAX code for a new table (my fact table is 'fTable'):

``````Date Table =
CALENDAR ( MIN ( fTable[Course_Date] ), MAX ( fTable[Course_Date] ) ),
"MonthNum", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"QuarterNum", QUARTER ( [Date] ),
"Quarter", "Qtr" & QUARTER ( [Date] ),
"YYYYQ",
YEAR ( [Date] ) * 10
+ QUARTER ( [Date] ),
"Year & Quarter",
YEAR ( [Date] ) & " " & "Qtr"
& QUARTER ( [Date] ),
"Year", YEAR ( [Date] )
)
`````` Sort the Month field by the MonthNum field; sort the Quarter field by the QuarterNum field; sort the Year & Quarter field by the "YYYYQ" field.

Create a one-to-many single relationship between the 'Date Table'Date] and the fTable[Course_date]. The model looks like this: Now create the measure for the matrix:

``````Quarter Cumul Countrows =
CALCULATE (
COUNTROWS ( fTable ),
FILTER (
ALL ( 'Date Table' ),
'Date Table'[YYYYQ] <= MAX ( 'Date Table'[YYYYQ] )
)
)
``````

Use the 'Date Table'[Year & Quarter] field for the matrix columns and the [Quarter Cumul Countrows] measure as values to get Sample PBIX file attached

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

3 REPLIES 3  Super User

Here is one way. First create a date table for the model (recommended best practice). You can use the following DAX code for a new table (my fact table is 'fTable'):

``````Date Table =
CALENDAR ( MIN ( fTable[Course_Date] ), MAX ( fTable[Course_Date] ) ),
"MonthNum", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"QuarterNum", QUARTER ( [Date] ),
"Quarter", "Qtr" & QUARTER ( [Date] ),
"YYYYQ",
YEAR ( [Date] ) * 10
+ QUARTER ( [Date] ),
"Year & Quarter",
YEAR ( [Date] ) & " " & "Qtr"
& QUARTER ( [Date] ),
"Year", YEAR ( [Date] )
)
`````` Sort the Month field by the MonthNum field; sort the Quarter field by the QuarterNum field; sort the Year & Quarter field by the "YYYYQ" field.

Create a one-to-many single relationship between the 'Date Table'Date] and the fTable[Course_date]. The model looks like this: Now create the measure for the matrix:

``````Quarter Cumul Countrows =
CALCULATE (
COUNTROWS ( fTable ),
FILTER (
ALL ( 'Date Table' ),
'Date Table'[YYYYQ] <= MAX ( 'Date Table'[YYYYQ] )
)
)
``````

Use the 'Date Table'[Year & Quarter] field for the matrix columns and the [Quarter Cumul Countrows] measure as values to get Sample PBIX file attached

In doing so, you are also helping me. Thank you!

Proud to be a Super User! Regular Visitor

Awesome, its working.

Thanks for the swift and easy solution Regular Visitor   