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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
lhern_ndez
Helper I
Helper I

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

Hello,

 

I have a model where I produce the following Matrix:

 

Matrix.jpg

 

 

 

 

 

 

 

 

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!

 

Luis

 

1 ACCEPTED SOLUTION

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

11 REPLIES 11
kohlivinayak
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:

 

Table_NOI.jpg

 

 

 

 

 

 

 

Thanks,

 

Luis

 

can you share your tables with sample data.

if these tables are related measure should still work.

 

Hello,

 

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:

 

COA.jpg

 

 

 

 

 

 

 

 

 

 

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:

 

DATA.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

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!

 

Luis

 

Hello @kohlivinayak

 

Any help will be appreciated!

 

Thanks!

 

Luis

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,

 

Luis

can you share your tables with sample data.

if these tables are related measure should still work.

v-shex-msft
Community Support
Community Support

Hi @lhern_ndez,

 

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

 

Regards,

Xiaoxin Sheng

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

Hello 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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