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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

Measure: how to calculate difference of filtered row fields shown as column in a matrix



I have a model where I produce the following Matrix:











The rows, columns, values and filters are as follow:


Filters and columns.jpg
























ACCOUNT CATEGORY is a column in a table, I add that field as column in the Matrix and then I filter: Operating Revenue and Operating Expense. I need to calculate the difference of those two filtered items: Operating Revenues - Operating Expenses and show it to the right.


Thanks in advance for the help!





Hi @lhern_ndez


Sorry for late reply.


Filter your measures for revenue and expence also - you will have to create a total of 6 measures








View solution in original post

Resolver I
Resolver I

I have faced the same situation and because it is a matrix you cannot just calculate a measure and add it in values.

Because Values will be repeated for all the categories resulting in displaying difference twice, one for revenue and one for expense, which is usually not required.


Then i tried to do it in the database by calculating and union it to the actual table but it resulted in the same thing getting two difference column.

Screen Shot 2018-07-04 at 12.52.32 PM.png









Only possible option i could find was create a measure for every column in the matrix. Eventually matrix will be gone and you will have a table.


This might not be your requirement but this was what we ended up with. I hope we get a better solution otherwise you can always use this one


sample i created

Screen Shot 2018-07-04 at 12.44.55 PM.png









Measures created for the above table


Actual Revenue = CALCULATE(MAX(Data[Actual]),FILTER(Data,Data[Category]="Revenue"))

Actual Expense = CALCULATE(MAX(Data[Actual]),FILTER(Data,Data[Category]="Expense"))


Actual Difference = [Actual Revenue] - [Actual Expense]


Simillarly you can create measures for other columns.

Hello kohlivinayak,


Thanks for your answer, the only detail is that in my case, Actual, Budget and ActVsBud are already measure (data coming from a related table, not the table that contains the column ACCOUNT CATEGORY, example as follow:


Actual = CALCULATE(sum(AspenData[Amount reporting]),FILTER(AspenData,AspenData[Accounting Book Type]="Actual"))


Thus, I see your measures are assuming the data are in the same table, is there any other possible solution?


I would like the table to look like this:














can you share your tables with sample data.

if these tables are related measure should still work.




This is the an extract from the table PSPAccount, which is basically a chart of accounts, there are different categories in the column ACCOUNT CATEGORY and as you can see, the are not values:













This is an extract from the table AspenData, which are the transactions, the tables are related by the Partner Account Number, this is the table where the values (Column AMOUNT) are and the ACCOUNT CATEGORY field is not here, please note that Actual or Budget are in the column ACCOUNTING BOOK TYPE:
















I tried your formulas but did not work as the values are in a different table, maybe you can tweak them?


Thanks for the help!




Hello @kohlivinayak


Any help will be appreciated!





Hi @lhern_ndez


Sorry for late reply.


Filter your measures for revenue and expence also - you will have to create a total of 6 measures








Hi @lhern_ndez


Please mark it as solution if it has worked.

Hello kohlivinayak,


Sorry for taking long, I was traveling.


I appreciate your help,



can you share your tables with sample data.

if these tables are related measure should still work.

Community Support
Community Support

Hi @lhern_ndez,


Please share some sample data/pbix file with expected result to help us clear your requirement.



Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.






















Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors