cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Alibek24 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.

Thanks in advance!

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. Hope this can help you!

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 ,

I have a little confused about your requirement.

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

Thanks in advance! 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. Hope this can help you!

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.