cancel
Showing results for
Did you mean:
Frequent Visitor

## Get MAX value of aggregated SUM from matrix row

Hello everybody,

I'm stacked on one thing, I can not find any ways to get max value from matrix row. It looks as below:

So, I have two dates, and the number of customers coming back. These numbers are count of customer
IDs, and to calculate retention rate I need to divide every months number by first month number. For March I have 1445 new customers, and in April 362 of them come back. I need second matrix, with 100% in March, 362/1445=25%, and so on for first row. And then same thing for second row.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Get MAX value of aggregated SUM from matrix row

Hi @Alibek24 ,

By my tests with your smaller sample data, you could create the two calculated columns to achieve your desired output.

```Column =
VAR mindate =
CALCULATE (
MIN ( 'Table1'[Date2] ),
FILTER ( 'Table1', [Date1] = EARLIER ( Table1[Date1] ) )
)
RETURN
CALCULATE (
MAX ( 'Table1'[Value] ),
FILTER ( 'Table1', 'Table1'[Date2] = mindate )
)
Column 2 =
VAR a = CALCULATE ( MAX ( 'Table1'[Value] ) ) RETURN DIVIDE ( a, [Column] )
```

Here is the output.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team

## Re: Get MAX value of aggregated SUM from matrix row

Hi @Alibek24 ,

From your title, it seems that you want to get the max value of aggregated SUM from matrix.

I need second matrix, with 100% in March, 362/1445=25%, and so on for first row. And then same thing for second row.

However, in your description, it seems that you want to create another matrix with the percentage of every months number by first month number.

If it is convenient, could you share the data sample which could reproduce your scenario and your desired output so that we could help further on it?

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Get MAX value of aggregated SUM from matrix row

So, let's consider a smaller example.

The dataset for this matrix can be found here.

What I want to get, is a so-called retention rate for every month.

It is easy to do it in Excel, and I already solved this problem in SQL query step.

But I'm still curious how to do it in DAX, cause I believe it can be done in Power BI.

Community Support Team

## Re: Get MAX value of aggregated SUM from matrix row

Hi @Alibek24 ,

By my tests with your smaller sample data, you could create the two calculated columns to achieve your desired output.

```Column =
VAR mindate =
CALCULATE (
MIN ( 'Table1'[Date2] ),
FILTER ( 'Table1', [Date1] = EARLIER ( Table1[Date1] ) )
)
RETURN
CALCULATE (
MAX ( 'Table1'[Value] ),
FILTER ( 'Table1', 'Table1'[Date2] = mindate )
)
Column 2 =
VAR a = CALCULATE ( MAX ( 'Table1'[Value] ) ) RETURN DIVIDE ( a, [Column] )
```

Here is the output.