how to get onboarding rate with dax?

[JJJL]

Hi, 

I need to get the onboarding rate via dax but I can't do it correctly.

dataset as belows, I can't put all raw data here.

step1. get AuM group by Client , short name, yymm and sort , like picture 1

step2. calculate onborading rate like pricture 1(Excel), B,C,E,F22 is formula, the result I need is B,C,E,F21

region	Client	Short	AuM	yymm
TW	ALL LIFE Insu	(G) GSF	0.03	Feb-25
TW	ALL LIFE Insu	(G) EED	0.04	Feb-25
TW	ALL LIFE Insu	(I) AI	0.05	Feb-25
TW	ALL LIFE Insu	(G) GSF	0.05	Feb-25
TW	ALL LIFE Insu	(G) US SD	0.05	Feb-25
TW	ALL LIFE Insu	(I) GMAC	0.08	Feb-25
TW	ALL LIFE Insu	(G) US SD	0.08	Feb-25
TW	ALL LIFE Insu	(I) AI	0.11	Feb-25
TW	ALL LIFE Insu	(I) AI	0.22	Feb-25
TW	ALL LIFE Insu	(G) EED	0.22	Feb-25
TW	ALL LIFE Insu	(I) GIC	0.24	Feb-25
TW	ALL LIFE Insu	(I) GMAC	0.28	Feb-25
TW	ALL LIFE Insu	(I) AI	0.32	Feb-25
TW	ALL LIFE Insu	(G) Glb Inc	0.34	Feb-25
TW	ALL LIFE Insu	(G) IG	0.49	Feb-25
TW	ALL LIFE Insu	(I) OIN	0.60	Feb-25
TW	ALL LIFE Insu	(G) US SD	0.77	Feb-25
TW	ALL LIFE Insu	(I) GIC	1.48	Feb-25
TW	ALL LIFE Insu	(I) AI	1.87	Feb-25
TW	ALL LIFE Insu	(G) IG	2.67	Feb-25
TW	ALL LIFE Insu	(G) EED	2.77	Feb-25
TW	ALL LIFE Insu	(I) GIC	2.83	Feb-25
TW	ALL LIFE Insu	(G) IG	5.55	Feb-25
TW	ALL LIFE Insu	(G) IG	5.59	Feb-25
TW	ALL LIFE Insu	(G) Glb Inc	14.52	Feb-25
TW	ALL LIFE Insu	(G) GAI	15.28	Feb-25
TW	ALL LIFE Insu	(G) Glb Inc	17.11	Feb-25
TW	ALL LIFE Insu	(I) OIN	36.69	Feb-25
TW	ALL LIFE Insu	(G) IG	86.40	Feb-25
TW	ALL LIFE Insu	(G) IG	215.49	Feb-25
TW	ALL LIFE Insu	(G) IG	428.80	Feb-25
TW	ALL LIFE Insu	(G) IG	670.47	Feb-25
TW	ALL LIFE Insu	(G) IG	2581.20	Feb-25
TW	AN Inv Advisors Co., Ltd.	(G) US SD	0.00	Feb-25
TW	AN Inv Advisors Co., Ltd.	(G) EED	0.01	Feb-25
TW	AN Inv Advisors Co., Ltd.	(I) BS GlbEQ	0.02	Feb-25
TW	AN Inv Advisors Co., Ltd.	(I) CFT	0.02	Feb-25
TW	AN Inv Advisors Co., Ltd.	(I) US IGC	0.03	Feb-25
TW	AN ...	(I) US IGC	0.04	Feb-25
TW	AN ...	(G) GSF	0.06	Feb-25
TW	AN ...	(I) GMAC	0.06	Feb-25
TW	AN ...	(I) GIC	0.07	Feb-25
TW	AN ...	(I) US IGC	0.07	Feb-25
TW	AN ...	(I) AI	0.08	Feb-25
TW	AN ...	(I) AI	0.09	Feb-25
TW	AN ...	(G) EED	0.14	Feb-25
TW	AN ...	(G) GSF	0.18	Feb-25
TW	AN ...	(G) GSF	0.19	Feb-25
TW	AN ...	(I) AI	0.20	Feb-25
TW	AN ...	(I) GIC	0.22	Feb-25
TW	AN ...	(I) AI	0.28	Feb-25
TW	AN ...	(I) AI	0.28	Feb-25
TW	AN ...	(I) AI	0.34	Feb-25
TW	AN ...	(G) US SD	0.34	Feb-25
TW	BNP PS CAR  LIFE Insu	(G) IG	2.41	Jan-25
TW	BNP PS CAR  LIFE Insu	(G) Glb Inc	2.55	Jan-25
TW	BNP PS CAR  LIFE Insu	(G) Glb Inc	8.32	Jan-25
TW	BNP PS CAR  LIFE Insu	(G) Glb Inc	16.58	Jan-25
TW	BNP PS CAR  LIFE Insu	(G) IG	40.68	Jan-25
TW	BNP PS CAR  LIFE Insu	(G) IG	204.64	Jan-25
TW	BNP PS CAR  LIFE Insu	(G) IG	281.09	Jan-25
TW	CY United BK	(G) GSF	0.02	Jan-25
TW	CY United BK	(I) OIN	0.02	Jan-25
TW	CY United BK	(I) AI	0.03	Jan-25
TW	CY United BK	(G) US SD	0.04	Jan-25
TW	CY United BK	(G) EED	0.04	Jan-25
TW	CY United BK	(I) AI	0.05	Jan-25
TW	CY United BK	(G) Glb Inc	0.06	Jan-25
TW	CY United BK	(I) AI	0.08	Jan-25
TW	CY United BK	(G) US SD	0.08	Jan-25
TW	CY United BK	(G) Glb Inc	0.09	Jan-25
TW	CY United BK	(G) EED	0.09	Jan-25
TW	CY United BK	(G) IG	0.12	Jan-25
TW	CY United BK	(G) GSF	0.12	Jan-25
TW	CY United BK	(G) GAI	0.15	Jan-25
TW	CY United BK	(G) IG	0.17	Jan-25
TW	CY United BK	(G) Glb Inc	0.20	Jan-25
TW	CY United BK	(I) AI	0.20	Jan-25
TW	CY United BK	(I) AI	0.21	Jan-25
TW	CY United BK	(I) AI	0.22	Jan-25
TW	CY United BK	(G) Glb Inc	0.25	Jan-25
TW	CY United BK	(G) GAI	0.25	Jan-25
TW	CY United BK	(I) GMAC	0.26	Jan-25
TW	CY United BK	(G) US SD	0.35	Jan-25
TW	CY United BK	(I) OIN	0.37	Jan-25
TW	CY United BK	(I) GMAC	0.38	Jan-25
TW	CY United BK	(I) CFT	0.48	Jan-25
TW	CY United BK	(G) EED	0.54	Jan-25
TW	CY United BK	(I) AI	0.70	Jan-25
TW	CY United BK	(G) US SD	0.73	Jan-25
TW	CY United BK	(I) AI	0.85	Jan-25
TW	CY United BK	(G) US SD	0.86	Jan-25
TW	CY United BK	(G) IG	1.30	Jan-25
TW	CY United BK	(G) IG	1.41	Jan-25

[v-hashadapu]

Hi @JJJL , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[v-hashadapu]

Hi @JJJL , We are closing this thread as we haven't heard from you in a while, according to our follow-up policy. If you have any more questions, please start a new thread on the Microsoft Fabric Community Forum. We will be happy to assist you! Thank you for being part of the community!

[v-hashadapu]

Hi @JJJL , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @JJJL , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[bhanu_gautam]

@JJJL , Try using

Create a new table to aggregate AuM by Client, Short, and yymm:

AggregatedAuM =
SUMMARIZE(
'YourTable',
'YourTable'[Client],
'YourTable'[Short],
'YourTable'[yymm],
"TotalAuM", SUM('YourTable'[AuM])
)

The onboarding rate can be calculated as the percentage change in AuM from one period to the next. Assuming you want to calculate the month-over-month change, you can use the following DAX measure:

OnboardingRate =
VAR CurrentMonthAuM =
CALCULATE(
SUM('YourTable'[AuM]),
FILTER(
'YourTable',
'YourTable'[yymm] = MAX('YourTable'[yymm])
)
)
VAR PreviousMonthAuM =
CALCULATE(
SUM('YourTable'[AuM]),
FILTER(
'YourTable',
'YourTable'[yymm] = EDATE(MAX('YourTable'[yymm]), -1)
)
)
RETURN
DIVIDE(CurrentMonthAuM - PreviousMonthAuM, PreviousMonthAuM, 0)

[JJJL]

thank you so much for your reply soon😀

It is not a concept of MoM, I think the picture is more clearly as below, when I choice Feb. 2025, it will show H19:J23, when I choice Jan, 2025, it will show K19:M23 in power bi Matrixes table.

I will create a slicer in Power bi interface.

[v-hashadapu]

Hi @JJJL , Thank you for reaching out to the Microsoft Community Forum.

Please try below steps:

1. First, we need to aggregate the AuM by Client, Short, and yymm. This table will serve as the foundation for ranking and calculations, ensuring we have the summed AuM for each combination.

AggregatedAuM =

SUMMARIZE(

    'YourTable',

    'YourTable'[Client],

    'YourTable'[Short],

    'YourTable'[yymm],

    "TotalAuM", SUM('YourTable'[AuM])

)

2. Next, add a calculated column to the AggregatedAuM table to rank the funds (Short) within each Client and yymm based on their AuM. This ensures the top 3 and top 6 funds are identified correctly for the selected period.

RankByAuM =

RANKX(

    FILTER(

        ALLSELECTED(AggregatedAuM),

        AggregatedAuM[Client] = EARLIER(AggregatedAuM[Client]) &&

        AggregatedAuM[yymm] = EARLIER(AggregatedAuM[yymm])

    ),

    AggregatedAuM[TotalAuM],

    ,

    DESC,

    Dense

)

3. We need a measure to calculate the total AuM for the current Client and yymm, which will be the denominator for the onboarding rate. This measure ensures the total is scoped correctly within the Matrix and slicer context.

TotalAuM =

CALCULATE(

    SUM('YourTable'[AuM]),

    ALLEXCEPT('YourTable', 'YourTable'[Client], 'YourTable'[yymm])

)

4. Create measures to sum the AuM for the top 3 and top 6 funds based on the RankByAuM column. These will be the numerators for the onboarding rate calculations.

Top3AuM =

CALCULATE(

    SUM(AggregatedAuM[TotalAuM]),

    FILTER(

        AggregatedAuM,

        AggregatedAuM[RankByAuM] <= 3

    )

)

Top6AuM =

CALCULATE(

    SUM(AggregatedAuM[TotalAuM]),

    FILTER(

        AggregatedAuM,

        AggregatedAuM[RankByAuM] <= 6

    )

)

5. Now, calculate the onboarding rates by dividing the top 3 and top 6 AuM by the total AuM. These measures will give you the percentages shown in your screenshot.

OnboardingRate_Top3 =

DIVIDE(

    [Top3AuM],

    [TotalAuM],

    0

)

OnboardingRate_Top6 =

DIVIDE(

    [Top6AuM],

    [TotalAuM],

    0

)

Set up your Power BI report. Add a slicer for yymm to filter the data. Create three Matrix visuals: one for all funds (Rows: Client and Short, Values: TotalAuM), one for the top 3 funds (filter RankByAuM <= 3, Rows: Client and Short, Values: TotalAuM and OnboardingRate_Top3), and one for the top 6 funds (filter RankByAuM <= 6, Rows: Client and Short, Values: TotalAuM and OnboardingRate_Top6). Add a Card visual with TotalAuM to show the total AuM for the selected Client and yymm. Format the onboarding rate measures as percentages.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[JJJL]

Thank you for your solution. 

The result I need is not aum rate, I need is count aum and get rate.

Here is my solution, but I'm not sure how to improve it.

Step 1 is create table as below:

Total_AUM_By_Short_Name_Region_YYMM =

SUMMARIZE(

    'sheet 1',

    'sheet 1'[Vehicle Short Name],

    'sheet 1'[YYMM],

    'sheet 1'[Client Name],

    'sheet 1'[Region],

    "Total_AUM", SUM('sheet 1'[AuM]) 

)

The second step is to create a table as shown below: the summary should return a table similar to your solution, and I will assign rankings to obtain the top 10 AUM and all data.

Top_10_AUM =

VAR RankedClients =

    ADDCOLUMNS(

        SUMMARIZE(

            'sheet 1',

            'sheet 1'[Region],

            'sheet 1'[Client Name],

            'sheet 1'[YYMM],

            "Total_AUM", SUM('sheet 1'[AuM])

        ),

        "Rank", RANKX(

            FILTER(

                SUMMARIZE(

                    'sheet 1',

                    'sheet 1'[Client Name],

                    'sheet 1'[Region],

                    'sheet 1'[YYMM],

                    "Total_AUM", SUM('sheet 1'[AuM])

                ),

                'sheet 1'[Region] = EARLIER('sheet 1'[Region]) &&

                'sheet 1'[YYMM] = EARLIER('sheet 1'[YYMM])

            ),

            [Total_AUM],

            ,

            DESC

        )

    )

RETURN

    FILTER(RankedClients, [Rank] <= 10)

step 3 is create table to get the aum number from table 

Total_AUM_By_Short_Name_Region_YYMM (top 10)

Filtered_AUM_By_Top_Clients =

FILTER(

    Total_AUM_By_Short_Name_Region_YYMM,

    'Total_AUM_By_Short_Name_Region_YYMM'[Client Name] & 'Total_AUM_By_Short_Name_Region_YYMM'[YYMM] & 'Total_AUM_By_Short_Name_Region_YYMM'[region]IN

        SELECTCOLUMNS(

            Top_10_AUM,

            "CombinedKey",

            [Client Name] & [YYMM] &[Region]

        )

)

the final step is count how many rows in the table Filtered_AUM_By_Top_Clients group by short name,yymm,region

Client_Count_By_Short_Name_Top_10_AUM =

VAR ShortNames = VALUES('sheet 1'[Vehicle Short Name])

VAR YYMM_Region =

    SUMMARIZE(

        Filtered_AUM_By_Top_Clients,

        'Filtered_AUM_By_Top_Clients'[YYMM],

        'Filtered_AUM_By_Top_Clients'[Region]

    )

VAR AllCombinations =

    CROSSJOIN(ShortNames, YYMM_Region)

RETURN

    ADDCOLUMNS(

        AllCombinations,

        "Client_Count",

        COALESCE(

            CALCULATE(

                COUNTROWS(Filtered_AUM_By_Top_Clients),

                Filtered_AUM_By_Top_Clients[Vehicle Short Name] = EARLIER('sheet 1'[Vehicle Short Name]),

                Filtered_AUM_By_Top_Clients[YYMM] = EARLIER('Filtered_AUM_By_Top_Clients'[YYMM]),

                Filtered_AUM_By_Top_Clients[Region] = EARLIER('Filtered_AUM_By_Top_Clients'[Region]),

                Filtered_AUM_By_Top_Clients[total_AuM] >= 0

            ),

            0

        ),  

        "Client_Rate",

        COALESCE(

            DIVIDE(

                CALCULATE(

                    COUNTROWS(Filtered_AUM_By_Top_Clients),

                    Filtered_AUM_By_Top_Clients[Vehicle Short Name] = EARLIER('sheet 1'[Vehicle Short Name]),

                    Filtered_AUM_By_Top_Clients[YYMM] = EARLIER('Filtered_AUM_By_Top_Clients'[YYMM]),

                    Filtered_AUM_By_Top_Clients[Region] = EARLIER('Filtered_AUM_By_Top_Clients'[Region]),

                    Filtered_AUM_By_Top_Clients[total_AuM] >= 0

                ),

                10,

                0

            ),

            0

        )

    )

The result is as blue mark, is correct, but it seems complicated....

 

This is the top 10 algorithms, but I also need the top 20 and 50. I don’t know if there is a better way to write it. My design seems very complicated....🤔

[v-hashadapu]

Hi @JJJL , Please check the attached .pbix file for your reference. 

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[v-hashadapu]

Hi @JJJL , Thank you for reaching out to the Microsoft Community Forum.

Please try below:

First, we aggregate the AuM by Client Name, Vehicle Short Name, YYMM, and Region. This table summarizes the data and serves as the foundation for all calculations.

Total_AUM_By_Short_Name_Region_YYMM =

SUMMARIZE(

    'sheet 1',

    'sheet 1'[Client Name],

    'sheet 1'[Vehicle Short Name],

    'sheet 1'[YYMM],

    'sheet 1'[Region],

    "Total_AUM", SUM('sheet 1'[AuM])

)

Add a calculated column to the 'sheet 1' table to rank clients within each Region and YYMM based on their total AuM. This ranking ensures we can dynamically identify the top N clients.

Rank_Client_AuM =

RANKX(

    FILTER(

        ALLSELECTED('sheet 1'),

        'sheet 1'[Region] = EARLIER('sheet 1'[Region]) &&

        'sheet 1'[YYMM] = EARLIER('sheet 1'[YYMM])

    ),

    CALCULATE(SUM('sheet 1'[AuM])),

    ,

    DESC,

    Dense

)

Create a parameter table with values 10, 20, and 50 to allow users to select the top N threshold. This approach ensures flexibility without duplicating logic.

TopN_Parameter =

DATATABLE(

    "TopN", INTEGER,

    {

        {10},

        {20},

        {50}

    }

)

Use measures to dynamically count the clients in the top N and calculate the client rate (count divided by the selected top N). This method is efficient as it avoids unnecessary tables and leverages Power BI’s calculation engine.

Client_Count_TopN =

VAR SelectedTopN = SELECTEDVALUE(TopN_Parameter[TopN], 10)

RETURN

CALCULATE(

    COUNTROWS('sheet 1'),

    'sheet 1'[Rank_Client_AuM] <= SelectedTopN,

    'sheet 1'[AuM] >= 0,

    ALLEXCEPT('sheet 1', 'sheet 1'[Vehicle Short Name], 'sheet 1'[YYMM], 'sheet 1'[Region])

)

Client_Rate_TopN =

VAR SelectedTopN = SELECTEDVALUE(TopN_Parameter[TopN], 10)

RETURN

DIVIDE(

    [Client_Count_TopN],

    SelectedTopN,

    0

)

Add a slicer for YYMM to filter the data. Create a Matrix visual with YYMM and Vehicle Short Name in Rows, Client_Count_TopN and Client_Rate_TopN in Values, and filter Region to SEA. Add a slicer for TopN_Parameter[TopN] to switch between top 10, 20, and 50. Format Client_Rate_TopN as a percentage to match your screenshot.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.