Matrix Table Custom

[rbalza]

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!

[edhans]

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.

[rbalza]

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]
    )

[edhans]

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.

[rbalza]

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

[edhans]

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

[rbalza]

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

[edhans]

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. 

[edhans · Accepted solution]

@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.