cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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
Highlighted
Super User III
Super User III

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

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





Highlighted
Helper II
Helper II

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

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?

Highlighted
Helper II
Helper II

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

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

Highlighted
Super User III
Super User III

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

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
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors