cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alibek24 Frequent Visitor
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:

 

2019_03_15_15_05_09_retention_rate_report_Power_BI_Desktop.png

 

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

Capture.PNG

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
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
Frequent Visitor

Re: Get MAX value of aggregated SUM from matrix row

Hi @v-piga-msft 

 

So, let's consider a smaller example.

 

2019_03_18_14_20_32_Untitled_Power_BI_Desktop.png

The dataset for this matrix can be found here.

 

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

 

2019_03_18_14_31_29_base_Excel.png

 

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

Capture.PNG

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.