Matrix or Table

[udian]

Hi,

I am trying to achive the following layout:

 

I want to display Category and Sub Category in the rows and payment duration (with values <1H, <2H, <12H) in the columns and the # Payments measure. The drill option of the matrix is not good enough (there will be more then 2 fields on the rows eventually).

Any workarounds?

Thanks!

[Anonymous · Accepted solution]

You could create three separate measures instead of using the Payment Duration column in the columns section of the matrix.  Something like this should work:

[Less than 1 Hour] =
CALCULATE ( 
	[# of Payments measure], 
	Table[Payment Duration] = "<1H" 
)

Then you could keep everything in a table format, or use a matrix format and put the three measures in the values section.

[Isaacpickel]

Hi,

The easist way to solve this without creating more calculated fields is to turn the 'Stepped Layout' function off in the row headings section of the matrix formatting. It gives the matrix more of the classic pivot table feel that most people are after.

There's a good write up on how to use the matrix function on the Microsoft site.

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

[Anonymous · Accepted solution]

You could create three separate measures instead of using the Payment Duration column in the columns section of the matrix.  Something like this should work:

[Less than 1 Hour] =
CALCULATE ( 
	[# of Payments measure], 
	Table[Payment Duration] = "<1H" 
)

Then you could keep everything in a table format, or use a matrix format and put the three measures in the values section.

[udian]

The solution you suggested doesn't solve the problem because the time ranges that should be displayed in the columns change according to the SLA slicer.

For every SLA there are different time ranges so it's not possible to hard code it into the measure. 

The only thing that is fixed is the number of time ranges displayed - no matter what SLA is chosen there will always be 3 time ranges.

Thanks!

[v-juanli-msft · Accepted solution]

Hi @udian

In Matrix, when add “payment duration” to Columns field, the time ranges can’t change according to the SLA slicer. To meet your requirement, we need to create a calculated column which formula contains all time ranges and reflect changes to each selection in your slicer.

Best Regards

Maggie