Forum Discussion
How to create a calculated row in a matrix?
Hi,
I've tried searching for a solution to my question but I have not found an answer. What I'm trying to do is to simply have a "calculated row" at the bottom of Matrix Visualization that I can define a custom calculation on the amounts. I have a Matrix Visualization that shows Assets, Liabilities, Sales, Cost of Goods Sold, etc. broken down by months across the top. I would like to have a row at the bottom that would cacluate "Net Income" for me ("Income" minus (-) "Cost of Good Sold" & "Expenses" & "Taxes"). The "built in" Total column just sums up all the lines which is not what I want. Is there any way to acheive this with a Matrix Visualization? Maybe I need to choose a different chart type? I've looked at Measures but I'm not sure if those will work for my scenario? If anyone has any ideas, please let me know.
Thanks!!
Greg_Deckler ibach You can create a custom formula that evaluates the last Total row ( based on the filter context ) and changes the formula or leave it blank, depending what rows you have on matrix and what other cross filtering you will have..
i.e if you have months on rows you can change the grand total row with a new measure like [Custom Income] = IF ( HASONEVALUE(Dates[Month]; [SumOfIncome] ; [Net Income]) or IF(COUNTROWS(VALUES[Dates[Month])>1.........)
5 Replies
Sounds like what you want is a measure with your formula in it and a Matrix visualization with your months as rows. Then you could have your measure as a column.
Thanks for the quick reply! :smileyvery-happy: So I would have to flip x & y around around. Is that that only way you see something like this working with a Matrix visualization? It would be nice to have it at the bottom if possible, but if not, it is what it is I guess.
Greg_Deckler ibach You can create a custom formula that evaluates the last Total row ( based on the filter context ) and changes the formula or leave it blank, depending what rows you have on matrix and what other cross filtering you will have..
i.e if you have months on rows you can change the grand total row with a new measure like [Custom Income] = IF ( HASONEVALUE(Dates[Month]; [SumOfIncome] ; [Net Income]) or IF(COUNTROWS(VALUES[Dates[Month])>1.........)
Hi did anyone find a Solution for this, I am also looking for a way to add a calculated mesure in tha last row - after the grand total. then following rows will be my other calculations...
Dear All,
I am looking for a data visualization as below.
Date Sales 01.04.2017 87,155.15 02.04.2017 75,671.05 03.04.2017 53,656.58 04.04.2017 95,583.53 05.04.2017 81,566.91 06.04.2017 55,232.89 07.04.2017 19,664.03 08.04.2017 85,603.81 09.04.2017 92,895.74 10.04.2017 49,260.52 11.04.2017 110,258.68 12.04.2017 119,984.92 13.04.2017 74,187.71 14.04.2017 15.04.2017 80,569.64 16.04.2017 89,012.01 Grand Total 1,170,303.16 Total Budget 2,500,000.00 Difference -1,329,696.841 How this can be achived in Power Bi Destop?
Prathyush
