Accessible Reporting - Convert Matrix to Table, using Calculation Groups?

[Mark_Holland_GD]

Hi,

Our organisation work with individuals how use screen readers to understand our data. I want to replicate the following matrix as a table:

But I want to keep Month/Year in each column so the screen reader can move across the table and read all the information in the column, using Client Reach Measures as the Accessible Row Name to Refer to Row by.

How can I do this - keeping dates in columns? I considered using Calculation Groups to create a field for the most recent 12 months. Would there be a way to use these to achieve this output?

Thanks,

Mark

[v-venuppu · Accepted solution]

Hi @Mark_Holland_GD ,

You are absolutely right to look for a more accessible alternative - however, DAX does not support dynamic column names in a calculated table. This means we can't label columns like "July", "August", etc. based on date logic.

 Instead, the best approach is to return rows with dynamic month labels and use a table visual, which is screen reader friendly.

Here’s a simplified calculated table that does this:

FlattenedClientReach =
VAR MaxMonthID = MAX('01b: Calendar'[Month Year ID])
RETURN
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATE(
'AT - CLIENT REACH',
VAR Client = 'AT - CLIENT REACH'[Client ID]
RETURN
FILTER(
ADDCOLUMNS(
'01b: Calendar',
"ReachValue",
CALCULATE(
'AT - CLIENT REACH'[Measures - Client Reach],
'AT - CLIENT REACH'[Client ID] = Client
)
),
'01b: Calendar'[Month Year ID] > MaxMonthID - 12 &&
'01b: Calendar'[Month Year ID] <= MaxMonthID
)
),
"MonthName", FORMAT('01b: Calendar'[Date], "MMM YYYY")
),
"Client", [Client ID],
"Month", [MonthName],
"ClientReach", [ReachValue]
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Mark_Holland_GD]

Hi @v-venuppu ,

Thanks for this. While this solution does work, technically, it's not the best accessible experience for a user who uses a screenreader. If they want to compare 2 metrics - for example, Total Sales and Total Units Sold by month, they have to either sort the table by Month or by Metric Name. If they sort by Month they're able to compare metrics to each other for the month, but they can't get month on month easily. If they sort by Metric Name, they'll be able to see month on month change but not next to a comparable metric.

I'll check the suggestions blog to see if there's a request to make the matrix visual accessible. That would sort all this out.

Thanks for your help.

Mark

[v-venuppu]

Hi @Mark_Holland_GD ,

To show the column names as "May 2024", "Apr 2024", etc. instead of just MM/YY codes, you can create a new column in your Date table that formats the date.

1.Go to your Date table

2.Add a new column with this formula:

MonthYearText = FORMAT('Date'[Date], "MMM YYYY")

This will turn dates into labels like:

01/05/2024 - "May 2024"

01/04/2024 - "Apr 2024"

Now just use this MonthYearText field:
As your column headers in a matrix

Or as labels in a calculated table or measure

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Mark_Holland_GD]

Hi @v-venuppu ,

I'm not quite following this. I'm trying to avoid using the matrix visual for accessibility reasons - the table visual allows screenreaders to ready out the information more effectively for blind and low-vision users.

Here's the DAX for my calculated table:

Flattened Client Reach = 
VAR MaxMonthID = MAX('01b: Calendar'[Month Year ID]) + 1
RETURN
ADDCOLUMNS('AT - CLIENT REACH',
"July", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 11),
"August", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 10),
"September", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 9),
"October", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 8),
"November", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 7),
"December", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 6),
"January", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 5),
"February", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 4),
"March", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 3),
"April", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 2),
"May", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID - 1),
"June", CALCULATE('AT - CLIENT REACH'[Measures - Client Reach], '01b: Calendar'[Month Year ID] = MaxMonthID))

How can I change the "July" name to be dynamic, so it will show the MM/YY name from my date table that relates to MaxMonthID - 11?

[v-venuppu]

Hi @Mark_Holland_GD ,

Thank you for the Response..!!

Yes, it is possible to dynamically create columns for recent months based on your Month-Year ID or a date field. You can achieve this by creating a calculated table that automatically pulls the last N months (e.g., last 12 or 24), without manually updating it each month.

Let us Assume:

You have a fact table called 'YourDataTable'

You have a related 'Date' table with a field 'MonthYearID' (e.g., 202405 for May 2024)

There’s a value column 'Value' you want to sum

1.Create a measure or variable in the calculated table that grabs the most recent MonthYearID:

VAR MaxMonthID = MAX('Date'[MonthYearID])

2.Generate dynamic calculated table

FlattenedClientReach =
VAR MaxMonthID = MAX('Date'[MonthYearID])
RETURN
ADDCOLUMNS(
'Metrics',
"M1", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[MonthYearID] = MaxMonthID),
"M2", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[MonthYearID] = MaxMonthID - 1),
"M3", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[MonthYearID] = MaxMonthID - 2),
...
"M12", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[MonthYearID] = MaxMonthID - 11)
)

This creates a wide table with 12 columns for the last 12 months.
You can name the columns "M1", "M2" etc. or use dynamic naming by formatting MonthYear text values, but this way ensures it adjusts when new data arrives.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Mark_Holland_GD]

Great, thanks @v-venuppu .

Just a final piece, how do I use dynamic naming here? I'm not sure how to format the MM/YY value to text for the column header.

[v-venuppu]

Hi @Mark_Holland_GD ,

Thank you for your Response..!!

Yes, if you're looking to display values by Month and Financial Year in a flattened table format (with one column per month), then you will need to create a column for each unique combination of Month and Year - like July 2024, August 2024, ..., June 2025.

You can do this by slightly modifying the calculated table DAX to include the full Month-Year in each column header. For example:

FlattenedClientReach =
ADDCOLUMNS(
'Metrics',
"Jul 2024", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 7, 'Date'[Year] = 2024),
"Aug 2024", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 8, 'Date'[Year] = 2024),
...
"Jun 2025", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 6, 'Date'[Year] = 2025)
)

This will create a column for each month-year period, allowing the Table visual to show fixed columns per month, making it accessible for screen readers.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Mark_Holland_GD]

Thanks @v-venuppu ,

Would there be a way to make this a bit more dynamic? I've got a MM/YY ID column that gives each Month Year and ID with the highest being the most recent. Could I add in the MAX calculation here to look for the most recent month, then do MAX - 1 to get the previous one? Then I could create 24 of these to show the past 2 months, which would interact with a MM/YY filter on the page to allow users to move between months.

Is that possible?

Otherwise I'll need to keep creating new columns each month when we get new data.

[v-venuppu]

Hi @Mark_Holland_GD ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu]

Hi @Mark_Holland_GD ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @shafiz_p for the prompt response.

you can generate a calculated table where each metric and month value is flattened into a single row per metric, with months as distinct columns.

Steps to Implement (Using DAX and Calculation Groups):

1.Create a Base Metrics Table:
If not already available, ensure you have a table that lists all your metrics like:

Metrics = DISTINCT('YourDataTable'[Client Reach Measures])

2.Create a Calculated Table (Flattened Table Format):
Use a DAX expression like this to pivot month data into columns:

FlattenedClientReach =
ADDCOLUMNS(
'Metrics',
"July", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 7),
"August", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 8),
"September", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 9),
"October", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 10),
"November", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 11),
"December", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 12),
"January", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 1),
"February", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 2),
"March", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 3),
"April", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 4),
"May", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 5),
"June", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[Month] = 6)
)

Replace 'YourDataTable'[Value] with the appropriate measure field (e.g. leads, enquiries, etc.).

3.Use in a Table Visual:
Now, use the new FlattenedClientReach table in a Table visual. This visual will now:

• Treat each month as a distinct column.
• Use Client Reach Measures as the row headers.
• Allow screen readers to scan across months column-wise.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Mark_Holland_GD]

Hi @v-venuppu ,

If I'm looking at Months and Financial Year, with this approach, would I need to create a column for each MM/YY value? 

[shafiz_p]

Hi @Mark_Holland_GD , this output is possible using matrix visual. See this image:

place year/month in column and measure in values and dimension in rows. Then go to values > turn on switch values to rows. Set layout to table. Dril on both rows and column. You will get your desired output.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[Mark_Holland_GD]

Hi @shafiz_p ,

That's how my matrix is already set up. The issue is it's not accessible - so when a screen reader reads the matrix, it doesn't identify the main columns to read from, until in a table visual.

For example, the user should be able to move to column February 2025. Moving down that column the screen reader would use the Client Reach Measures column as the Reading Row and would read this column followed by the vaue in February 2025, then move down to the next. This way the blind user is able to get an idea of the performance for February across all metrics. But the matrix doesn't do this.

So I want to have a table with a column for each month in order to do this. I was thinking of trying calculation groups to do this, creating one metric for each month. But I'm not sure how I could do this or how to set it up.

Does that help?

[shafiz_p]

I don't think you will get your desired output using table visual, for this you need to use matrix visual either use switch value to rows or not.

In table , all fields are treated as columns. So using calculation group in table for each month as an item, it will not show as individual column as you want rather it will show month name as row item.

To set up a calculation group, go to model view, select calculation group, name it.  Then create seperate Item for each month. For example, for January2025:

January2025 = 
CALCULATE(
    SELECTEDMEASURE(), 
    'Calendar'[Month] = 1 && 'Calendar'[Year] = 2025
)

Repeat this process for all 12 months.

Place calculation group in column and client research measures in values. Also, client research measures column in rows.

Try and check if this helps or not.

Thanks,

[Mark_Holland_GD]

Thanks @shafiz_p ,

Do you know if there's an alternative way to get a column in a Table Visual to read out the values each month for a metric?

What about creating a calculated table and putting the values from calculation groups into the calculated table?