Forum Discussion

Rickmaurinus's avatar
Rickmaurinus
Helper V
5 years ago
Solved

Using ALL with CALCULATE in a snowflake model

Hi all, 


I'm struggling on a measure for my Profit and Loss statement.  The model itself is very basic: 

 

 

In a matrix, I show the Level 1 Description in the rows. 

 

And normally for my profit and loss I use 2 basic measures. One for individual accounts = 

CALCULATE(
     [ Total Amount],
     'Chart of Accounts'[Reporting Type] = "Profit and Loss" )

​ 
and one for subtotals: 

CALCULATE( [Total Amount],
     FILTER(
          ALL( 'Chart of Accounts'[Level 1 Description], 'Chart of Accounts'[Level 1 Sort] ),
               'Chart of Accounts'[Level 1 Sort] <= MAX( 'Chart of Accounts'[Level 1 Sort] ) ),
     KEEPFILTERS( 'Chart of Accounts'[Reporting Type] = "Profit and Loss" ) )

 

My challenge is the following: I would like to correct some numbers. And the numbers to correct are bundled in the Chart of Accounts -> Level 1 Description called 'Normalisations'. So my idea is to change the filter context, to only include the 'Normalisations' from the Chart of Accounts. This will filter the General Ledger Hierarchy, which in turn filters General Ledger Transactions. I need to different filter context, because also within other categories than 'Normalisations' , I need to be able to subtract the amount. 

 

Marco and Alberto from SQLBI taught me that it's better to change the filter context choosing specific columns for ALL() in your CALCULATE statement, instead of referencing a table. So what I tried is: 

PnL Measure =
CALCULATE( [Total Amount],
     FILTER( ALL( 'Chart of Accounts'[Level 1 Description] ),
    'Chart of Accounts'[Level 1 Description] = "Normalisations" ) )

 

This results are just showing the 'Normalisation' amount in the row 'Normalisation' of the Level 1 Description. (picture follows)

 

I had expected it to show the 'Normalisation' amount, on ALL the rows of Level 1 Description. After all, I used ALL to provide each of those rows with the entire table of Level 1 Description combinations. 

 

A measure that does work = 

 

PnL Measure 1 =
CALCULATE( [Total Amount],
     FILTER( ALL( 'Chart of Accounts' ),
     'Chart of Accounts'[Level 1 Description] = "Normalisations" ) )

 

But this uses the ALL function on the entire Chart of Accounts table. Which I tried to prevent. Below is the result: 

 

 

I would like the result of PnL Measure 1 without referencing the entire table. Can anyone suggest a solution here? Or explain why one measure works, and the other doesn't ?

 

Thanks again,

 

Rick

 

7 Replies

  • Rickmaurinus 

    This should work for you. :

    PnL Measure = 
    CALCULATE( 
        [Total Amount],
        KEEPFILTERS('Chart of Accounts'[Level 1 Description] = "Normalisations") 
    
    ) 

     

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

    • Rickmaurinus's avatar
      Rickmaurinus
      Helper V

      Hi Fowmy ,

       

      In this case, the result of this measure, is identical to. 

       

      CALCULATE( [Total Amount],
           FILTER( ALL( 'Chart of Accounts'[Level 1 Description] ),
          'Chart of Accounts'[Level 1 Description] = "Normalisations" ) )

       

      It only shows the Normalisation amount, on the row with normalisations. I would like it to show on each row. 

       

      Any other suggestions?

      • Fowmy's avatar
        Fowmy
        Super User

        Rickmaurinus 

        Then, remove the REMOVEFILTERS:

        PnL Measure = 
        CALCULATE( 
            [Total Amount],
            'Chart of Accounts'[Level 1 Description] 
        ) 

         

        ________________________

        If my answer was helpful, please consider Accept it as the solution to help the other members find it

        Click on the Thumbs-Up icon if you like this reply 🙂

        YouTube  LinkedIn