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
AltusTellus
Helper III
Helper III

Matrix measure in column shows totals in stead of the filtered amount

Hi all,

 

I've an issue with a matrix table. Let's start with what I have at this moment:

 

I added a table with KPI's with measures for calculating the sum of values per G/L, e.g. 

AmountRevenue = CALCULATE(SUM(tb_ReportingBalance[Amount PL]); FILTER(tb_GLAccounts; tb_GLAccounts[Type] = 110))

The number "110" indicates that the G/L is of the type "Revenue". The table 'ReportingBalance' is linked to my 'dimdate' table so I can filter by year and period.
 
In my table with KPI's I have added a column [Combinatie Omschrijving] for the descriptions of the KPI so I can show the results in one columns. This looks like this:
KPI descriptions.png
In the table 'KPI' a column for the the results is added:
CombinationMeasureNum.png
 
All this brings me to this matrix, in which I filtered by the descriptions "Omzet" (=Revenue) and "EBITDA":
Matrix example.png
So the colums with the "Description" and "Realisation" are correct. But now, and this is my issue, I want to add a second measure from another tabel 'Budget'. In this table I have G/L's, Amounts et cetera, I linked the column with the G/L's to the table 'GLAccounts'. When I have a matrix with only the results from the table 'Budget' I have the correct values.
 
But I want to get the budgets and the realisation in one matrix as well. Is this possible? I think so, but I don't see how to solve this.
 
I hope someone can help me out with this issue, many thanks.
4 REPLIES 4
MFelix
Super User
Super User

Hi @AltusTellus ,

 

The setup of the relationship between Budget and dimension tables must be the same as for the Reporting balance table then you should make the measure similar also but based on the budget values.

 

Is it possible to have the budget done at the same dimensions table as your reporting balance?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi Miguel,

 

There are three tables involved:
1. 'ReportingBalance' with the column [GLAccountID] linked to 2. 'GLAccounts'

2. 'GLAccounts' with column [GLAccountID]

3. 'Budget' with the column [GLAccountID] linked to 2. 'GLAccounts'

 

I have one page with a matrix table with rows for the [GLAccountsID] and columns for Realisation versus Budget. The values works perfect.

 

But, in case of the table (or matrix) I send with my first comment, I get the wrong values. What am I doing wrong? Any other suggestions?

@MFelix Do you have a moment to reply? In advance many thnx.

Hi @AltusTellus ,

 

Is it possible to send out a mockup file with sample data?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.