cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Max of columns in Matrix

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.

4 REPLIES 4
Solution Supplier

@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

Frequent Visitor

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.

Solution Supplier

@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.

Super User

try like

MaxInCategory =

VAR _MaxAll =

CALCULATE(

MAX(TableName[Value]),

ALL(TableName[Year/Month])

)

VAR _Max = MAX(TableName[Value])

RETURN

IF(_MaxAll = _Max, _MaxAll)

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors