topic Re: Max of columns in Matrix in DAX Commands and Tips

Max of columns in Matrix

yuanye0710 — Fri, 09 Dec 2022 00:08:00 GMT

Hello, I'd like to plot a Matrix with date as columns, and another column at the end to calculate the max of thses dates for each category. User can use a slicer to select different dates, so the Max measure has to be dynamic too.

For example:

Data:

Category	Year/Month	Value
A	202201	100
A	202202	80
A	202203	97
B	202201	115
B	202203	118
B	202204	98
C	202202	106
C	202203	99
C	202204	79
C	202206	61
C	202208	62
D	202201	90
D	202202	115
D	202207	116
D	202208	115
E	202202	117
E	202205	112
F	202201	92

The final matrix will look like this, where Max equals to the Maximum of each date column for a given category. There will be a date slicer for user to choose different dates. The matrix will update according to user's date selection.

	202201	202202	202203	202204	202205	202206	202207	202208	Max
A
B
C
D
E
F

The problem is if I put the Year/Month in "Columns" field of the matrix, then my Max measure will be added to all date as well (instead of at the end). I also don't want to write a separate measure for each date because it will be a lot maintenance work.

Thanks.

Re: Max of columns in Matrix

FreemanZ — Fri, 09 Dec 2022 00:19:14 GMT

try like

MaxInCategory =

VAR _MaxAll =

CALCULATE(

MAX(TableName[Value]),

ALL(TableName[Year/Month])

)

VAR _Max = MAX(TableName[Value])

RETURN

IF(_MaxAll = _Max, _MaxAll)

Re: Max of columns in Matrix

Manoj_Nair — Fri, 09 Dec 2022 12:27:06 GMT

yuanye0710 pls try a simple DAX, straightforward solution or or even you can drag and drop the value in the metric column with max value- it works or you are after something else.

MAX = MAX(’max’[value])

Desired output =

Backend Data

Re: Max of columns in Matrix

yuanye0710 — Fri, 09 Dec 2022 13:07:03 GMT

Thanks but it's not what I was looking for. I plot this in Excel, each yellow cell is the sum of value for a given (category, year/month) pair, and then an additional Max column at the end to calculate the maximum of that row.

Re: Max of columns in Matrix

Manoj_Nair — Fri, 09 Dec 2022 16:27:50 GMT

yuanye0710 - try first summarising the table and then use the normal metrics. Screen grab explaining the back end steps.

The summarize table is made out of your sample datasets as shown below.