Matrix Visual with more than one row entry, but without herarchy and with grand total

[peatel]

Hi,

i'm still trying to use the matrix visual and avoid to pivot on data layer and then using the normal table visual.

I have read some entries made by users who had issues with some usage of that matrix in the past, but i'm curious whether there are new options today to resolve them.

So my scenario is the following:

I have the task to create a turnover report by brand and day. The customer wants to see the dates in vertical, the brand turnover each horizontaly. Day is based on a marked day table.

The issue is, that the customer wants me to include also the weekday name

So, by using the matrix visual, I have some options, but each of them comes with a disadvantage.

1. Put both fields on "rows" panel in the Visual. One has to disable stepped layout and disable row subtotals and drill to the lowest level. Otherwise the format looks horrible and there is no need to see a subtotal on level weekday, date, because it's the same entity and weekday only an additional information. Unfortunately in this case the grand total is missing as well.

2. Having only one item in "rows" panel, then the layout looks OK, but the weekday is missing.

3. Add a custom column by concatenating weekday and date. Then the visual does not work because the Date table field is not the correct one and I have a year to year comparison in the visual, so I need a proper date field.

I'm wondering if there is a best  practice to solve this in 2024 .  🙂

Thank you very much in advance.

[audreygerred · Accepted solution]

Hello! You don't have to disable row subtotals in their entirety - you can choose which items you don't want the subtotal on, that way you do get to keep your total row. As far as turning off stepped layout and disabling specific subtotals - it only needs to be done once when you develop the report, so it won't negatively affect the consumer's experience.

Here is an example with all the subtotals on: 

Here it is with them off, but grand total still shows:

To achieve this, expand Row subtotals (and leave it set to on), when you expand you will see the option to apply settings to per rows level - turn this on:

Next, select the bottom item that appears in the row level drop down and below it you will see a box with Rows - Show subtotal - turn this off. Basically - if all you want is the grand total showing, turn off everything in the row level except for the first item in the list.

In my example, I have Day, Month, and Quarter off, this leaves me with the grand total row (which I have at the top for ease of seeing it quickly, but you can leave it at the bottom).

[peatel]

Hi,

thank you very much. It works exept the fact that now the user can use the -/+ node with the effect that the workday is displayed or not. Not really nice, but acceptable.

Again, thanks a lot for the quick support 🙂

[audreygerred · Accepted solution]

Hello! You are welcome! You can turn the +/- off in the Row headers formatting.

My visual does not have the +/- turned on: 

[audreygerred · Accepted solution]

Hello! You don't have to disable row subtotals in their entirety - you can choose which items you don't want the subtotal on, that way you do get to keep your total row. As far as turning off stepped layout and disabling specific subtotals - it only needs to be done once when you develop the report, so it won't negatively affect the consumer's experience.

Here is an example with all the subtotals on: 

Here it is with them off, but grand total still shows:

To achieve this, expand Row subtotals (and leave it set to on), when you expand you will see the option to apply settings to per rows level - turn this on:

Next, select the bottom item that appears in the row level drop down and below it you will see a box with Rows - Show subtotal - turn this off. Basically - if all you want is the grand total showing, turn off everything in the row level except for the first item in the list.

In my example, I have Day, Month, and Quarter off, this leaves me with the grand total row (which I have at the top for ease of seeing it quickly, but you can leave it at the bottom).