topic Re: Matrix Table Custom in DAX Commands and Tips

Matrix Table Custom

rbalza — Thu, 03 Jun 2021 23:55:08 GMT

Hello PowerBi Friends,

How can I make a custom matrix table where I don't want to include the budget column in each month and view it on the total column only. Is there any way to do it like turning it on and off or something? Thanks everyone!

Re: Matrix Table Custom

edhans — Fri, 04 Jun 2021 00:09:53 GMT

Use ISINSCOPE. So say your budget is on the Department level and you only want to see it in the total row:

= IF( ISINSCOPE( TableName[Departments] ), BLANK(), Measure or Expression here )

If the department is in the scope (it is visible on the row in the matrix), it will return blank. The department will not be in scope on a total row, so it will return the total.

Re: Matrix Table Custom

rbalza — Fri, 04 Jun 2021 00:19:21 GMT

Hi edhans., thanks for your help. I actually use two Dax measures and not sure how to incorporate them with what you have suggested. See below the structure and Dax measures that I have used.

Actual = CALCULATE ( SUM ( 'Invoices'[Line Amount Calculation] ), FILTER ( 'Accounts', Accounts[Class] = "Revenue" || Accounts[Class] = "Expense" ) ) - [P&L - Credit Notes] + [P&L (Journals)]

Budget = VAR DaysinContext = COUNTROWS ( Dates ) VAR DaysinMonth = CALCULATE ( COUNTROWS ( Dates ), ALL ( Dates ), VALUES ( Dates[Month & Year] ) ) VAR CurrentMonth = SELECTEDVALUE ( Dates[Month Name] ) VAR MonthlyBudgetAmounts = [Total Full Budget] RETURN IF ( OR ( HASONEVALUE ( Dates[Date] ), HASONEVALUE ( Dates[Month Name] ) ), DIVIDE ( DaysinContext, DaysinMonth, 0 ) * MonthlyBudgetAmounts, [Total Full Budget] )

Re: Matrix Table Custom

edhans — Fri, 04 Jun 2021 00:46:35 GMT

You just put your measure in mine as an expression. So for actual, it would be this I think - hard to know exactly as you are showing pieces of your visual, not the whole thing. I'd rather see the whole thing but you can blur the numbers.

Actual = VAR varActual = CALCULATE( SUM( 'Invoices'[Line Amount Calculation] ), FILTER( 'Accounts', Accounts[Class] = "Revenue" || Accounts[Class] = "Expense" ) ) - [P&L - Credit Notes] + [P&L (Journals)] RETURN IF( ISINSCOPE( TableName[Account Type] ), BLANK(), varActual )

Budget is a bit trickier because I don't know what is going on with your HASONEVALUE functions, but this should work - but it could be tweaked:

Budget = VAR DaysinContext = COUNTROWS( Dates ) VAR DaysinMonth = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[Month & Year] ) ) VAR CurrentMonth = SELECTEDVALUE( Dates[Month Name] ) VAR MonthlyBudgetAmounts = [Total Full Budget] VAR varBudget = IF( OR( HASONEVALUE( Dates[Date] ), HASONEVALUE( Dates[Month Name] ) ), DIVIDE( DaysinContext, DaysinMonth, 0 ) * MonthlyBudgetAmounts, [Total Full Budget] ) VAR Result = IF( ISINSCOPE( TableName[Account Type] ), BLANK(), varBudget ) RETURN Result

To really troubleshoot need to see much more, and preferably a PBIX. But do you see how I am using it in those examples? You clearly have a good handle on the DAX here, it may just be ISINSCOPE is new to you. If you can use HASONEVALUE you should be able to adopt ISINSCOPE. It is usually an easier way to hide/show totals than the old HASONEVALUE and HASONEFILTER tricks.

Re: Matrix Table Custom

rbalza — Fri, 04 Jun 2021 01:05:16 GMT

Hi edhans , really appreciated your help on this, and yes, not familiar with the ISINSCOPE yet. Here's my matrix table.

Re: Matrix Table Custom

edhans — Fri, 04 Jun 2021 01:29:13 GMT

Ok, try my Actual measure. You need to use the right table name. If it doesn't work swap out Account Type for Name.

Re: Matrix Table Custom

rbalza — Fri, 04 Jun 2021 02:02:18 GMT

Hi edhans it returns nothing. and..the budget still on the month columns. where it should be on the YTD column only 🙂

Re: Matrix Table Custom

edhans — Fri, 04 Jun 2021 02:45:45 GMT

Sorry - I was trying to remove it from the rows, I see now you only want the budget to show up at the column level. I was going the wrong direction. Use my measure, but use ISINSCOPE(Date[YearMonth]) column. Whatever it is that is returning May 2021, June 2021.

If that doesn't work, I'll need the PBIX file, which you can share with me via PM if it is confidential data if you are ok with that. Otherwise, I'm playing hit/miss here.

Re: Matrix Table Custom

edhans — Fri, 04 Jun 2021 16:29:27 GMT

rbalza - so it turns out you cannot do what you want. I opened PBID this morning. I can supress the values, but in a matrix, the column will still show up as blank, which isn't desirable.

That is unfortunate. On a row, it would suppress the row value. The Matrix isn't as flexible as a Power Pivot table is in Excel.

Here is my PBIX if you want to see how ISINSCOPE is used.