Using ALL with CALCULATE in a snowflake model

[Rickmaurinus]

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

[Rickmaurinus · Accepted solution]

I've found the issue. When using a column to sort your values, this sort order stay intact when using ALL() formulas. By including the sort column(s) in the ALL() formula, it behaves as expected. You can read more about this here: 

https://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/solution/

Thanks for the help all!

Rick

[Fowmy]

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

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]

@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

[Rickmaurinus]

@Fowmy 

Thanks for your responses. Just like you, I would expect this to solve the problem. However, with this exact formula:

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

I still don't get the number repeated for each line of Level 1 Description. I have tried this earlier too.

My guess is, that a snowflake model, requires you to handle it differently. But I'm not completely sure why. 

[Fowmy]

@Rickmaurinus 

The measure should show the same value for all rows as you are overwriting the current filters.

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

Can you check your matrix, the Column you have used is Level 1 NOT  Level 1 Description. Have you renamed it in the visual?

Also, check if you have pulled the fields from the right dimension tables, If you are referring a field in the measure, the field is supposed to be there in the matrix. 

if you can share the PBIX file after removing confidential information, I can check it and figure out

________________________

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]

Hi @Fowmy 

Let me see if I can cook up a model that I can share. Would definitely be easier. 

Best,

Rick