Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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
Actual expense = CALCULATE(sum(AspenData[Amount reporting]),FILTER(AspenData,AspenData[Accounting Book Type]="Actual"),Filter(OTHER TABLE, Account Category = ''OperatingExpense'))
Actual revenue = CALCULATE(sum(AspenData[Amount reporting]),FILTER(AspenData,AspenData[Accounting Book Type]="Actual" ,Filter(OTHER TABLE, Account Category = ''OperatingExpense'))
And then the difference
Remove anything from column property of the matrix
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.
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
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:
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
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
Actual expense = CALCULATE(sum(AspenData[Amount reporting]),FILTER(AspenData,AspenData[Accounting Book Type]="Actual"),Filter(OTHER TABLE, Account Category = ''OperatingExpense'))
Actual revenue = CALCULATE(sum(AspenData[Amount reporting]),FILTER(AspenData,AspenData[Accounting Book Type]="Actual" ,Filter(OTHER TABLE, Account Category = ''OperatingExpense'))
And then the difference
Remove anything from column property of the matrix
can you share your tables with sample data.
if these tables are related measure should still work.
Hi @lhern_ndez,
Please share some sample data/pbix file with expected result to help us clear your requirement.
Regards,
Xiaoxin Sheng
Hello v-shex-msft,
Thanks for your answer, what I need is the below, see in yellow:
Where NOI should be a measure that substract Operating Revenue - Operating Expense.
Please note that Actual, Budget and BudVsAct are already measures, example as follow:
Actual = CALCULATE(sum(AspenData[Amount reporting]),FILTER(AspenData,AspenData[Accounting Book Type]="Actual"))
Thanks!
Luis
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
88 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |