Forum Discussion

ronaldbalza2023's avatar
ronaldbalza2023
Continued Contributor
4 years ago
Solved

Removing columns with zero values - matrix table

Hi everyone, having a hard time trying to figure out this one. Appreciate  the help on this!

Question : How can I remove the columns that are having zero values in Matrix table? 

I have filter on the visual however, it filters only on the rows that has total zeros in it.

Here's what inside the visualization

 

Here are the measures:

P&L = 
(
    CALCULATE (
        SUM ( 'Invoices'[Line Amount FX Calculation] ),
        FILTER (
            'Accounts',
            Accounts[Class] = "Revenue"
                || Accounts[Class] = "Expense"
        ), USERELATIONSHIP( Invoices[Invoice Line ID], 'Tracking Category CONNECTIONS'[ID])
    )
) - [P&L - Credit Notes] + [P&L (Journals)
P&L - Credit Notes = 
CALCULATE (
    SUM ( 'Credit Notes'[Line Amount Credit Note Calculation FX] ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( 'Credit Notes'[Credit Note Line ID], 'Tracking Category CONNECTIONS'[ID])
)
P&L (Journals) = 
CALCULATE ( 0 - ( SUM ( 'Journals'[Net Amount FX] ) ),
    FILTER ( 'Journals', 'Journals'[Split] = "JOURNALS" ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( Journals[ID], 'Tracking Category CONNECTIONS'[ID])
)

 

Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    ronaldbalza2023 , change this one

     

    P&L (Journals) = 
CALCULATE ( -1* ( SUM ( 'Journals'[Net Amount FX] ) ),
    FILTER ( 'Journals', 'Journals'[Split] = "JOURNALS" ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( Journals[ID], 'Tracking Category CONNECTIONS'[ID])
)

     

     

    Also on top of you measures you can create measures like

     

    P&L (Journals) new= if([P&L (Journals)] =0, blank(), [P&L (Journals)])

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    ronaldbalza2023 , change this one

     

    P&L (Journals) = 
CALCULATE ( -1* ( SUM ( 'Journals'[Net Amount FX] ) ),
    FILTER ( 'Journals', 'Journals'[Split] = "JOURNALS" ),
    FILTER (
        'Accounts',
        'Accounts'[Class] = "Revenue"
            || Accounts[Class] = "Expense"
    ), USERELATIONSHIP( Journals[ID], 'Tracking Category CONNECTIONS'[ID])
)

     

     

    Also on top of you measures you can create measures like

     

    P&L (Journals) new= if([P&L (Journals)] =0, blank(), [P&L (Journals)])

    • ronaldbalza2023's avatar
      ronaldbalza2023
      Continued Contributor
      4 years ago

      Hi amitchandak works like a charm, thanks! However, the zeroes that has a NIL value on rows have gone. How can I replace the blank rows with zero?

       

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        ronaldbalza2023 , I doubt you can have both.

        For that we need force, Like in this example I added +0, all dimension values will come and then I forced the date range again

         

        0 between range
        Measure = var _1= SUM(Opportunity[Opportunity count]) +0
        var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
        var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
        return
        CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))