Hi Team,I need support in implementing the following requirement in Power BI using DAX.Source DataThe data is received from Excel in a monthly format with columns such as:DateBU WBSGroup CurrencyFinancial MonthG_RatioL_Ratio Expected Outputattached in excelBusiness LogicApril CalculationCalculate the total Group Currency for both BU- G and L for April.Allocate the total using the month's ratios:G Value = April Total × G_RatioL Value = April Total × L_RatioMay CalculationCalculate the cumulative Group Currency for April + May.Allocate the cumulative total using May's ratios:Cumulative G = (April + May Total) × G_RatioCumulative L = (April + May Total) × L_RatioDerive the monthly value by subtracting the previous month's allocated value:May G = Cumulative G − April GMay L = Cumulative L − April LSubsequent MonthsApply the same cumulative allocation logic:Calculate cumulative total up to the current month.Multiply by the current month's ratio.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.

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

Helper V

2 months ago

Solved

Custom Matrix Visual

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

Calculate the total Group Currency for both BU- G and L for April.
Allocate the total using the month's ratios:
- G Value = April Total × G_Ratio
- L Value = April Total × L_Ratio

May Calculation

Calculate the cumulative Group Currency for April + May.
Allocate the cumulative total using May's ratios:
- Cumulative G = (April + May Total) × G_Ratio
- Cumulative L = (April + May Total) × L_Ratio
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:

Calculate cumulative total up to the current month.
Multiply by the current month's ratio.
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.

v-dineshya
2 months ago
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

custom_matrix.pbix51 KB

5 Replies

Kedar_Pande

Super User

2 months ago

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

Helper V

2 months ago

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
Community Support
2 months ago
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

custom_matrix.pbix51 KB

Forum Discussion

Custom Matrix Visual

Source Data

Expected Output
attached in excel

Business Logic

April Calculation

May Calculation

Subsequent Months

5 Replies

Related content

Customize subtotal in matrix

Customized Matrix Visual

Custom Visual

Matrix Visual Duality

Power Matrix visual