Freezing cell to calculate result in Power BI similar to excel

[sangeetaraman]

Hi Everyone,

I'm trying to make matrix visual with following calculation as done in excel. Ideally I want the "B%*A Qty" column to be caluclated using a measure (by applying the "Formula" column). For every multiplication the subtotal should be multiplied with. It is easy to freeze the subtotals in the A Qty column to achive this in excel.

Can anyone please guide me? Thanks in advance.

Plant name	B%	A Qty	B%*A Qty	Formula
Plant1	1.00%	1200	24	=1%*2400
Plant1	80.00%	200	1920	=80%*2400
Plant1	19.00%	1000	456	=19%*2400
Plant1 Total		2400.00
Plant2	45%	800.00	805.5	=45%*1790
Plant2	20.00%	950	358	=20%*1790
Plant2	35.00%	40	626.5	=35%*1790
Plant2 Total		1790.00

Rgds

Sangeeta

[Anonymous · Accepted solution]

Hi @sangeetaraman 

You can create a measure

Measure = var a=SUMX(FILTER(ALLSELECTED('Table'),[Plant name] in VALUES('Table'[Plant name])),[A Qty])
return SUMX('Table',[B%]*a)

Output

Best Regards!

Yolo Zhu

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