Forum Discussion
Hide effectively sublevel in matrix
Hi !
I am currently creating an Income Statement, and I am having some performance issues with my central table. To give some context, I have a fact table, which contains all the entries, each linked to a specific account, a table which contains all the indicator names, with a unique identifier, and a table which contains all the accounts, each linked to a specific indicator.
Entry :
| Date | AccountNo | Amount | Type (ACT or BGT) |
KPIDim :
| Id | Name | Position |
AccountDim :
| No | Name | KPIId |
The relationships are as follows:
KPIDim[Id] -> AccountDim[KPIId]
AccountDim[No] -> Entry[AccountNo]
Then, I created a measure for each indicator. Some of them use the KPIId column of the AccountDim table directly, and others, like gross margin for example, simply aggregate the measure of other indicators.
ActualSales = CALCULATE(SUM(Entry[Amount]), Entry[Type]="ACT", KPIDim[Id]="sales")
ActualCosts = CALCULATE(SUM(Entry[Amount]), Entry[Type]="ACT", KPIDim[Id]="costs")
ActualMargin = [ActualSales] - [ActualCosts]
With a final measure that selects the correct indicator, with a switch case:
SelectedIndicator = SWITCH(SELECTEDVALUE(KPIDim[Id],
"sales", ActualSales,
"costs", ActualCosts,
"margin", ActualMargin,
BLANK())
The table I want to create uses PowerBI's Matrix visual and contains:
Rows:
- Name of indicator (e.g. sales or gross margin)
- Account the indicator contains
Columns:
- Month
Values :
- Actual amount
- Budgeted amount
The question I'm now asking myself is how to get the table to display the accounts (sub-level) under the indicator ONLY if it contains them directly. This would be the case for sales and costs, but not for gross profit. So we'd need a table like this (here only the actual value is displayed):
| January | February | March | |
| Sales | 10 | 12 | 15 |
Account A | 6 | 7 | 7 |
Account B | 4 | 5 | 8 |
| |||
Costs | 3 | 5 | 7 |
Account C | 1 | 3 | 4 |
Account D | 2 | 2 | 3 |
| |||
Gross margin | 7 | 7 | 8 |
But for now, the result is as follows:
| January | February | March | |
| Sales | 10 | 12 | 15 |
Account A | 6 | 7 | 7 |
Account B | 4 | 5 | 8 |
| |||
Costs | 3 | 5 | 7 |
Account C | 1 | 3 | 4 |
Account D | 2 | 2 | 3 |
| |||
Gross margin | 7 | 7 | 8 |
Account A | 6 | 7 | 7 |
Account B | 4 | 5 | 8 |
Account C | 1 | 3 | 4 |
Account D | 2 | 2 | 3 |
I've managed to achieve this result by using a calculation group, which forces the accounts to be hidden for certain indicators, but the table is very slow to refresh when I select another year. The elements of the calculation group are as follows:
So I've come here to ask your opinion on this technique, and if of course you have any cleaner, better suggestions. To give a better idea, I'd like to do the same thing as in this YouTube tutorial: (64) The anatomy of an income statement matrix in Power BI - YouTube
I think the main issue explaining why the refresh of my matrix was so slow, is that I computed measures that were "imbricated". I completely reshaped my model, so that the rules for computing my indicators are applied in columns of an AccountDim table. Then, by setting the correct relationships, the matrix is easy to build and performances are great !
3 Replies
Hi benjos23 ,
You can try the following measure：
Hide = var _column= {"grossSales", "netSales", "materialLoad", "inventoryChange", "merchandiseLoad", "margin", "margin2", "ebitda", "ebit", "operatingIncome", "netIncomeAfterTax", "cashflow" } var _table= SUMMARIZE(ALL('AccountDim'),[Name of indicator],[Account the indicator contains],[Id],[Month],"Value_True",SUMX(FILTER('AccountDim',NOT('AccountDim'[Id]) in _column),[Value]) ) return IF( HASONEVALUE('AccountDim'[Account the indicator contains]) , SUMX( FILTER(_table,[Name of indicator]=MAX([Name of indicator])&&[Account the indicator contains]=MAX([Account the indicator contains])&&[Month]=MAX([Month])),[Value_True]) , IF( HASONEVALUE('AccountDim'[Name of indicator])&&NOT(HASONEVALUE('AccountDim'[Account the indicator contains])),SUM('AccountDim'[Value]),BLANK()) )If the dax above doesn't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Anonymous,
Thank you for your answer.
Actually, the model you created does not really look like to what I gave in my message, but I have the feeling the idea behind the solution you provided is the same as mine. I think I will stick to the method I have found, even if it is not very fast... I don't really find any other solution here, unfortunately.I think the main issue explaining why the refresh of my matrix was so slow, is that I computed measures that were "imbricated". I completely reshaped my model, so that the rules for computing my indicators are applied in columns of an AccountDim table. Then, by setting the correct relationships, the matrix is easy to build and performances are great !
