distribute quantities in several parts

[Syndicate_Admin]

I have this little problem and I don't know how to solve it
The problem in a summarized way and with little data is this: I have the data as in the first table I want to have a result as in the second table, my problem is that I do not know how to distribute with formulas the "06 Expenses" that has to be divided by the total of the m2 (240) and then multiply by the m2 corresponding to each house (70,80,90) I have tried in several ways, but I couldn't.
thank you very much for your support

Description	Matter	m2	Prototype 1
01 Foundation	$ 300.00	70	house 1
02 Structure	$ 250.00	70	house 1
03 Finishes	$ 200.00	70	house 1
04 windows	$ 150.00	70	house 1
05 doors	$ 100.00	70	house 1
01 Foundation	$ 301.00	80	house 2
02 Structure	$ 251.00	80	house 2
03 Finishes	$ 201.00	80	house 2
04 windows	$ 151.00	80	house 2
05 doors	$ 101.00	80	house 2
01 Foundation	$ 302.00	90	house 3
02 Structure	$ 252.00	90	house 3
03 Finishes	$ 202.00	90	house 3
04 windows	$ 152.00	90	house 3
05 doors	$ 102.00	90	house 3
06 Expenses	$ 1,000.00	240	all

Row labels	house 1	house 2	house 3
01 Foundation	$ 300.00	$ 301.00	$ 302.00
02 Structure	$ 250.00	$ 251.00	$ 252.00
03 Finishes	$ 200.00	$ 201.00	$ 202.00
04 windows	$ 150.00	$ 151.00	$ 152.00
05 doors	$ 100.00	$ 101.00	$ 102.00
06 Expenses	$ 291.67	$ 333.33	$ 375.00
Total general	$ 1,291.67	$ 1,338.33	$ 1,385.00

I thank you in advance!

[v-angzheng-msft · Accepted solution]

Hi, @Syndicate_Admin |

Try to create a calculated table like this:

M_Table = 
var _m2_Pro=SUMMARIZE(FILTER(ALL('Table'),[Prototype 1]<>"all"),[m2],[Prototype 1])
var _Des_Mat=SUMMARIZE(FILTER(ALL('Table'),[Prototype 1]="all"),[Description])
var _t=CROSSJOIN(_m2_Pro,_Des_Mat)
var _add_Matter=
    SUMMARIZE(
        ADDCOLUMNS(_t,"Matter",CALCULATE(DIVIDE(MAX('Table'[Matter]),MAX('Table'[m2])),FILTER(ALL('Table'),'Table'[Description]=[Description]))*[m2]),[Description],[Matter],[m2],[Prototype 1])
return UNION(_add_Matter,'Table')

Then you can get the result with a matirx visual.
Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Syndicate_Admin , Try a new table like

var _1 = filter(distinct(selectcolumns(Table, "PR", Table[Prototype 1]) ),[PR]<> "all")
Var _2 = union(addcolumns(_1, "P1", _1[PR]) ,
addcolumns(_1, "P1", "all"))
var _3 = addcolumns( filter(generate(_2, Table), _2[PR] = [Prototype 1]), "New M2",if( [Prototype 1] = "all", [m2]/countrows(_1), [m2]))

Then you can use matrix viusal