Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
Hope this can help you!
Best Regards,
Cherry
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
Hi @v-piga-msft
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!
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |