Forum Discussion
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
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
7 Replies
- FowmySuper User
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 🙂
- RickmaurinusHelper 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?
- FowmySuper 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 🙂
- RickmaurinusHelper V
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