cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ibach Frequent Visitor
Frequent Visitor

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!!

1 ACCEPTED SOLUTION

Accepted Solutions
konstantinos Senior Member
Senior Member

Re: How to create a calculated row in a matrix?

@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.........)

 

 

 

 

 

 

Konstantinos Ioannou
5 REPLIES 5
Super User
Super User

Re: How to create a calculated row in a matrix?

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ibach Frequent Visitor
Frequent Visitor

Re: How to create a calculated row in a matrix?

Thanks for the quick reply! Smiley Very 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.

konstantinos Senior Member
Senior Member

Re: How to create a calculated row in a matrix?

@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.........)

 

 

 

 

 

 

Konstantinos Ioannou
Prathyush Frequent Visitor
Frequent Visitor

Re: How to create a calculated row in a matrix?

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...

Prathyush Frequent Visitor
Frequent Visitor

Re: How to create a calculated row in a matrix?

Dear All,

 

I am looking for a data visualization as below.

DateSales
01.04.201787,155.15
02.04.201775,671.05
03.04.201753,656.58
04.04.201795,583.53
05.04.201781,566.91
06.04.201755,232.89
07.04.201719,664.03
08.04.201785,603.81
09.04.201792,895.74
10.04.201749,260.52
11.04.2017110,258.68
12.04.2017119,984.92
13.04.201774,187.71
14.04.2017 
15.04.201780,569.64
16.04.201789,012.01
Grand Total1,170,303.16
Total Budget2,500,000.00
Difference-1,329,696.841

 

How this can be achived in Power Bi Destop?

 

Prathyush