Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alibek24
Regular 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

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.

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

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!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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