Change single cell in matrix column subtotal

Question

I have a matrix that looks somewhat like this,&nbsp;&nbsp;accounts&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Q1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Q2&nbsp; &nbsp; &nbsp; &nbsp; Q3&nbsp; &nbsp; &nbsp; &nbsp; Q4&nbsp; &nbsp; &nbsp; &nbsp;Full yearsales&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120&nbsp; &nbsp; &nbsp; &nbsp; 140&nbsp; &nbsp; &nbsp; 120&nbsp; &nbsp; &nbsp; 150&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 530OI&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 30&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20&nbsp; &nbsp; &nbsp; &nbsp;50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120Return on Sale&nbsp; &nbsp;25%&nbsp; &nbsp; &nbsp; &nbsp;14%&nbsp; &nbsp; &nbsp; 16%&nbsp; &nbsp; 33%&nbsp; &nbsp; &nbsp; &nbsp; 22%&nbsp;The 22% in return on sale is done because the matrix averages the 4 return on sales. the full year column is the column subtotal renamed to full year. i do not want the full year return on sale being calculated by averaging the 4 return on sales, rather, I want to have it be calculated by dividing full year OI by full year Sales. does anyone know how i would be able to change just that cell in a matrix to make it divide oi by sales rather than average it?&nbsp;

amitchandak · Answer

Anonymous , Return on Sale should be measure like
If sales or IO are meausres&nbsp;
Return on Sale = Divide([Sales], [OI])
&nbsp;
if the are values&nbsp; assuming you are using measure m1 = sum(Table[Value])
&nbsp;
if(Max(Table[Account]) &lt;&gt; "Return on Sale", [M1], divide(calculate([M1], Table[Account] = "sales") ,calculate([M1], Table[Account] = "OI")&nbsp; ) )
&nbsp;
&nbsp;
I have replaced few account total here
&nbsp;
Power BI How to get the P&amp;L formatting right: https://youtu.be/C9K8uVfthUU Power BI How to get two columns format Profit and Loss Statement(P&amp;L) right: https://youtu.be/WLg85yiMgHIhttps://medium.com/microsoft-power-bi/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e39ca822908a

anonymous · Answer

How/where would i input these directions for it to apply

Forum Discussion

Change single cell in matrix column subtotal

2 Replies

Related content

Customize subtotal in matrix

Matrix subtotal

Row subtotals blank

Single select slicer

how to create single totals