Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ibach
Regular 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
konstantinos
Memorable Member
Memorable Member

@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

View solution in original post

5 REPLIES 5
konstantinos
Memorable Member
Memorable Member

@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

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.

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

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.