Matrix visual not showing desired row subtotals based on date values

[Anonymous]

I have a matrix visual where in the rows I have a "Casus ID" and "Actie ID" and as values I have "loonwaardemeting (actiedoelstelling)" and "Startdatum",.

I sorted the visual using "Startdatum" (which is a date column).

What I want is that it shows the most recent "loonwaardemeting", so like the green part of the picture and not like the red part.

Is there a way to enforce this? or to otherwise completely disable the subtotals? (because if I choose the option to disable them, they're still there when I don't expand Casus ID)

[vs_7]

HI @Anonymous ,

try below steps:

To achieve the desired result in your matrix visual, you can apply sorting and subtotal settings. Here's what you can do:

1. Sorting: Make sure the "Startdatum" column is sorted in descending order. To do this, select the matrix visual, go to the "Modeling" tab in the Power BI ribbon, and click on the "Sort Ascending" or "Sort Descending" button, depending on your current sorting order.

2. Subtotals: You can disable subtotals for specific columns or for the entire visual. To disable subtotals for the "Casus ID" and "Actie ID" columns, follow these steps:

   • Select the matrix visual.
   • In the "Visualizations" pane, under the "Format" section, expand the "Subtotals" option.
   • Toggle off the "Subtotals" switch for both the "Casus ID" and "Actie ID" columns.

   Disabling subtotals for these columns will prevent the undesired red subtotal rows from appearing.

3. Aggregation: To display the most recent "loonwaardemeting" value, you need to ensure that the aggregation for that column is set to "Max" (assuming the column contains numeric or date values). Here's what you can do:

   • Select the matrix visual.
   • In the "Visualizations" pane, under the "Values" section, select the "loonwaardemeting (actiedoelstelling)" field.
   • In the "Modeling" tab of the Power BI ribbon, click on the "Summarize" dropdown.
   • Choose "Max" as the aggregation function for the "loonwaardemeting (actiedoelstelling)" column.

   This will ensure that the visual displays the maximum value (most recent) for the "loonwaardemeting" column instead of aggregating it in any other way.