Allocation of Fixed Costs Equally

[DSP VARMA]

Hi All,

As a new user of Power BI I am facing difficulties. I am attaching an excel sheet that has Raw Data and Required Results. I want the same result which is in Require Result using measures in Power BI Matrix Visual with Domain Slicers. Thanks in Advance

SGA for Power BI.xlsx
	A	B	C	D	E	F
1	Particular	A	B	C	D	Total
2	Marketing	3	7	23	0	33
3	Adm	43	102	352	7	504
4	Gen Admin	10	24	83	2	119
5	Per	880	2,085	7,168	145	10,278
6	Tra	-	-	-	-	-
7	IT exp	98	232	876	16	1,221
8	Wir Tel	11	25	88	2	125
9	Maintenance	-	-	-	-	-
10	Legal Exp	-	-	-	-	-
11	Rent	99	235	923	16	1,273
12	Consulting	23	56	191	4	274
Original RD

Cell Formulas
Range	Formula
F2:F12	F2	=SUM(B2:E2)

SGA for Power BI.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Domain	(All)
2
3	Sum of Value	Column Labels
4	Row Labels	01-06-2022	02-06-2022	03-06-2022	04-06-2022	05-06-2022	06-06-2022	07-06-2022	08-06-2022	09-06-2022	10-06-2022	11-06-2022	12-06-2022	13-06-2022	14-06-2022	15-06-2022	16-06-2022	17-06-2022	18-06-2022	19-06-2022	20-06-2022	21-06-2022	22-06-2022	23-06-2022	24-06-2022	25-06-2022	26-06-2022	27-06-2022	28-06-2022	29-06-2022	30-06-2022	Grand Total
5	Adm	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	504	15,124
6	Consulting	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	274	8,234
7	Gen Admin	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	119	3,581
8	IT exp	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	1,221	36,624
9	Legal Exp	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
10	Maintenance	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
11	Marketing	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	33	1,000
12	Per	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	10,278	308,335
13	Rent	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	1,273	38,200
14	Tra	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
15	Wir Tel	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	125	3,763
16	Grand Total	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	13,829	414,861
Required Result

 

[GraH]

You do not have dates in the raw data, so why do you add it in your required result? If it the same, why show the split over days?

Else, unpivot RD, selecting column A, right click and unpivot other columns (in Transform data/Power Query).
Filter the attribute column like <> Total. Since you can calculate those, you do not need them. (Best to replace both Attribute and Value by explicit names)
Load this table to the data model.
Add a measure like SUM(RD[Value]) in the data model.
Now build the matrix.

However -> without a date in your RD table, you will get cartesian products. You can repeat the values you obtain through PQ by creating a list of dates (List.Dates - PowerQuery M). Then expand this list. That way you can connect your RD to a calendar table. Doing so might complicate others things you are trying to achieve within PowerBI. I can't tell.
Maybe the same is possible through DAX too, but that I do not know.

 

[DSP VARMA]

You do not have dates in the raw data, so why do you add it in your required result? If it the same, why show the split over days?

Else, unpivot RD, selecting column A, right click and unpivot other columns (in Transform data/Power Query).
Filter the attribute column like <> Total. Since you can calculate those, you do not need them. (Best to replace both Attribute and Value by explicit names)
Load this table to the data model.
Add a measure like SUM(RD[Value]) in the data model.
Now build the matrix.

However -> without a date in your RD table, you will get cartesian products. You can repeat the values you obtain through PQ by creating a list of dates (List.Dates - PowerQuery M). Then expand this list. That way you can connect your RD to a calendar table. Doing so might complicate others things you are trying to achieve within PowerBI. I can't tell.
Maybe the same is possible through DAX too, but that I do not know.

Thanks for your reply. But the table without the dates is per day expenses for the complete month which are divided equally over the month. But I prefer the DAX formula for the solution.