Forum Discussion

rbalza's avatar
rbalza
Helper III
5 years ago
Solved

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!

 

 

DAX
Dax Help
Need Help DAX
  • edhans's avatar
    edhans
    5 years ago

    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. 

     

8 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    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's avatar
      rbalza
      Helper III
      5 years ago

      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's avatar
        edhans
        Community Champion
        5 years ago

        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.