Custom Matrix Visual

[Jessica_17]

Hi Team,

I need support in implementing the following requirement in Power BI using DAX.

Source Data

The data is received from Excel in a monthly format with columns such as:

• Date

• BU 

• WBS

• Group Currency

• Financial Month

• G_Ratio

• L_Ratio

Expected Output
attached in excel

Business Logic

April Calculation

1. Calculate the total Group Currency for both BU- G and L for April.

2. Allocate the total using the month's ratios:

   • G Value = April Total × G_Ratio

   • L Value = April Total × L_Ratio

May Calculation

1. Calculate the cumulative Group Currency for April + May.

2. Allocate the cumulative total using May's ratios:

   • Cumulative G = (April + May Total) × G_Ratio

   • Cumulative L = (April + May Total) × L_Ratio

3. Derive the monthly value by subtracting the previous month's allocated value:

   • May G = Cumulative G − April G

   • May L = Cumulative L − April L

Subsequent Months

Apply the same cumulative allocation logic:

1. Calculate cumulative total up to the current month.

2. Multiply by the current month's ratio.

3. Subtract the previous month's cumulative allocated value to arrive at the current month's value.

The requirement is to implement this cumulative allocation logic in Power BI using DAX and display the results in a matrix by month and BU.

Please let me know if any additional clarification is required.

[Kedar_Pande]

You need two measures:

Cumulative Allocated = 
VAR _ratio = SELECTEDVALUE('Data'[G_Ratio]) 
VAR _cumTotal = 
CALCULATE(
SUM('Data'[Group currency]),
FILTER(
ALL('Data'[financial month]),
'Data'[financial month] <= MAX('Data'[financial month])
)
)
RETURN _cumTotal * _ratio

Monthly Value = 
VAR _current = [Cumulative Allocated]
VAR _prior = 
CALCULATE(
[Cumulative Allocated],
FILTER(
ALL('Data'[financial month]),
'Data'[financial month] = MAX('Data'[financial month]) - 1
)
)
RETURN _current - COALESCE(_prior, 0)

The ratio differs by BU, so drive _ratio with the row's BU using G_Ratio for G and L_Ratio for L. Put financial month on columns and BU on rows in the matrix.

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

@Jessica_17

[Jessica_17]

Hello  ,

This is not providing the correct output as required.
Here is my sample data

Date	BU	WBS	Group Currency	Financial Month	G_Ratio	L_Ratio	SUB_GROUP
04-04-2026	G	234PGM	35	1	0.236	0.764	A
04-04-2026	G	23423PLL	345	1	0.236	0.764	A
04-04-2026	L	234PGM	234	1	0.236	0.764	A
04-04-2026	L	23423PLL	2342	1	0.236	0.764	A
10-04-2026	G	76876FG	4234	1	0.236	0.764	A
10-04-2026	G	89789GHN	534	1	0.236	0.764	A
10-04-2026	L	76876FG	645	1	0.236	0.764	A
10-04-2026	L	89789GHN	64	1	0.236	0.764	A
14-04-2026	G	868GHHB	56	1	0.236	0.764	B
14-04-2026	G	8798JKK	45	1	0.236	0.764	B
14-04-2026	L	868GHHB	234	1	0.236	0.764	B
14-04-2026	L	8798JKK	32	1	0.236	0.764	B
03-05-2026	G	3466HGJ	568	2	0.3859	0.6141	B
03-05-2026	G	8907HGY	465	2	0.3859	0.6141	B
03-05-2026	L	3466HGJ	46	2	0.3859	0.6141	B
03-05-2026	L	8907HGY	456456	2	0.3859	0.6141	B
15-05-2026	G	56765OLK	345345	2	0.3859	0.6141	C
15-05-2026	G	54587DGB	234	2	0.3859	0.6141	C
15-05-2026	L	56765OLK	324	2	0.3859	0.6141	C
15-05-2026	L	54587DGB	32	2	0.3859	0.6141	C
24-05-2026	G	6797UGM	765	2	0.3859	0.6141	C
24-05-2026	G	78656HSC	46	2	0.3859	0.6141	C
24-05-2026	L	6797UGM	234	2	0.3859	0.6141	C
24-05-2026	L	78656HSC	235	2	0.3859	0.6141	C
04-05-2026	G	234PGM	23	2	0.3859	0.6141	A
04-05-2026	G	23423PLL	567	2	0.3859	0.6141	A
04-05-2026	L	234PGM	234	2	0.3859	0.6141	A
04-05-2026	L	23423PLL	678	2	0.3859	0.6141	A
10-05-2026	G	76876FG	567	2	0.3859	0.6141	A
10-05-2026	G	89789GHN	45	2	0.3859	0.6141	A
10-05-2026	L	76876FG	678	2	0.3859	0.6141	A
10-05-2026	L	89789GHN	43	2	0.3859	0.6141	A
14-05-2026	G	868GHHB	6262	2	0.3859	0.6141	B
14-05-2026	G	8798JKK	959	2	0.3859	0.6141	B
14-05-2026	L	868GHHB	234	2	0.3859	0.6141	B
14-05-2026	L	8798JKK	598	2	0.3859	0.6141	B
03-04-2026	G	3466HGJ	652	1	0.236	0.764	B
03-04-2026	G	8907HGY	598	1	0.236	0.764	B
03-04-2026	L	3466HGJ	5984	1	0.236	0.764	B
03-04-2026	L	8907HGY	232	1	0.236	0.764	B
15-04-2026	G	56765OLK	295	1	0.236	0.764	C
15-04-2026	G	54587DGB	598	1	0.236	0.764	C
15-04-2026	L	56765OLK	52	1	0.236	0.764	C
15-04-2026	L	54587DGB	989	1	0.236	0.764	C
24-04-2026	G	6797UGM	9865	1	0.236	0.764	C
24-04-2026	G	78656HSC	959	1	0.236	0.764	C
24-04-2026	L	6797UGM	56	1	0.236	0.764	C
24-04-2026	L	78656HSC	6569	1	0.236	0.764	C

and here is my expected output for validation on BU level

for g it should work with g_ratio, for L it should work with l_ratio, and for rest where BU are not g or l, simply sum(group currency), also it should work with sum of g&L, not separately.

I have updated the excel as well with formula, you can refer that as well.

[v-dineshya · Accepted solution]

Hi @Jessica_17 ,

Thank you for reaching out to the Microsoft Community Forum. Please refer below output snap and attached .pbix file.

Please refer below main measures.

Test Month Value =

VAR CurrentBU =

    SELECTEDVALUE ( Fact[BU] )

RETURN

    SWITCH (

        TRUE (),

        CurrentBU IN { "G", "L" },

            [Current Cum Allocation]

                - COALESCE ( [Previous Cum Allocation], 0 ),

        [GC]

    )

Allocated Amount =

SWITCH (

    TRUE (),

    ISINSCOPE ( Fact[WBS] )

        && ISINSCOPE ( Fact[Financial Month] ),

        [Test Month Value],

    ISINSCOPE ( Fact[WBS] )

        && NOT ISINSCOPE ( Fact[Financial Month] ),

        SUMX (

            VALUES ( Fact[Financial Month] ),

            CALCULATE ( [Test Month Value] )

        ),

    ISINSCOPE ( Fact[BU] )

        && ISINSCOPE ( Fact[Financial Month] ),

        SUMX (

            VALUES ( Fact[WBS] ),

            CALCULATE ( [Test Month Value] )

        ),

    ISINSCOPE ( Fact[BU] )

        && NOT ISINSCOPE ( Fact[Financial Month] ),

        SUMX (

            VALUES ( Fact[WBS] ),

            CALCULATE (

                SUMX (

                    VALUES ( Fact[Financial Month] ),

                    CALCULATE ( [Test Month Value] )

                )

            )

        ),

    SUMX (

        VALUES ( Fact[BU] ),

        CALCULATE (

            SUMX (

                VALUES ( Fact[WBS] ),

                CALCULATE (

                    SUMX (

                        VALUES ( Fact[Financial Month] ),

                        CALCULATE ( [Test Month Value] )

                    )

                )

            )

        )

    )

)

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[Jessica_17]

Thanks @v-dineshya ,

It worked for me.

[Jessica_17]

I have updated the excel data as well little bit, its little different from what I pasted in above message.