Override category using CALCULATE and ALL for a P&L

[Rickmaurinus]

Hi there, 

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

In my Table 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.

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. 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. 

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: 

Can anyone suggest a solution here? Or at least explain why one measure works, and the other doesn't ?

Thanks again,

Rick

Edit: The measure showed Level 1 instead of Level 1 description. 

[Rickmaurinus · Accepted solution]

Since this thread was marked as spam, I reposted it. And it is now answered at: 

https://community.powerbi.com/t5/Desktop/Override-category-using-CALCULATE-and-ALL-for-a-P-amp-L/m-p...

[Fowmy]

@Rickmaurinus 

For the following measure, you can use VALUES which respects the existing filter on LEVEL1 and you do not iterate the whole table

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

 

In the following measure, I noticed that you are clearing filters on [Level 1 Description]  then filter [Level 1] . 

PnL Measure =
CALCULATE( [Total Amount],
     FILTER( ALL( 'Chart of Accounts'[Level 1 Description] ),
    'Chart of Accounts'[Level 1] = "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, 

Thanks for spotting that I cleared filters on [Level 1 Description] and filtered [Level1]. This was an error in describing my post. I changed the post description. The issue remains. 

I tried your suggestion with 

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

This results in that only Level 1 that is called 'Normalisations' shows the rightnumber. My goal is to show that number, no matter the level that's showing. 

The measure using ALL( 'Chart of Accounts' ) shows the result I want, yet it uses ALL() on the entire table. I'm curious if there is a way to use ALL( ) on a single/few columns and still get the result I want.

Best,

Rick

[Greg_Deckler]

@Rickmaurinus Try using ALLEXCEPT or REMOVEFILTERS instead of ALL.