Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rbalza
Helper III
Helper III

Matrix Table Custom

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!

rbalza_0-1622764376960.png

rbalza_1-1622764452557.png

 

 

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

edhans_0-1622824064544.png

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. 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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



rbalza_0-1622765750012.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

rbalza_0-1622768685364.png

 

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

rbalza_0-1622772085893.png

 

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

edhans_0-1622824064544.png

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. 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.