cancel
Showing results 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

## 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:

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

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
Resolver I

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

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

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

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.

Helper I

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:

Thanks,

Luis

Resolver I

can you share your tables with sample data.

if these tables are related measure should still work.

Helper I

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:

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!

Luis

Helper I

Hello @kohlivinayak

Any help will be appreciated!

Thanks!

Luis

Resolver I

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

Resolver I

Please mark it as solution if it has worked.

Helper I

Hello

Sorry for taking long, I was traveling.

Luis

Resolver I

can you share your tables with sample data.

if these tables are related measure should still work.

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

Hello

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.