Exclude GL account from equity

[Djdjwhao]

Hi

I'm trying to get some ratios to calculate without a certain P&L GL from the equity section for certain ratios.  I was able to get that to work for a few of the ratios that has the net or operating income via a calculation group but that same calculation group did not take out the excluded P&L item from the equity section. Please advise.  I used:  Calculate(selectedmeasure(), NOT chartofaccounts[GLnumber] IN {"000-9160-00"})

[v-kpoloju-msft]

Hi @Djdjwhao,
Thanks for sharing the details of your scenario. Also, thanks to @Shahid12523, for his inputs on thread.

From what you described, the reason your calculation group did not exclude the GL account from the equity section is that the equity measure itself is not wrapped with that filter logic. The calculation group works fine for your P&L measures (like Net/Operating Income), but equity will need a dedicated adjustment.

You can try creating a separate equity measure like this:

Equity (Excluding GL 9160) =
CALCULATE(
[Equity],
KEEPFILTERS(
NOT chartofaccounts[GLnumber] IN {"000-9160-00"}
)
)

Then use this adjusted equity measure inside your ratios. That way the unwanted GL account is consistently excluded.

Refer these links:
https://learn.microsoft.com/en-in/dax/calculate-function-dax
https://learn.microsoft.com/en-us/power-bi/transform-model/calculation-groups

Hope this helps. If you have any doubts regarding this, please feel free to ask here. We will be happy to help.

Thank you for using the Microsoft Community Forum.

[Djdjwhao]

Hi

It didn't work.  I don't know why.

[v-kpoloju-msft]

Hi @Djdjwhao,

Thank you for the follow-up question. Also, thank you for testing that out and letting us know. Since you mentioned it still did not work, could you confirm whether:
• You are seeing the same numbers as before (filter not applying), or
• You are running into an error message?

To help isolate the issue, try placing the [Equity (Excluding GL 9160)] measure in a simple table along with the GLnumber column. That way, you can check if the unwanted GL account is still included.

If the first approach did not work in your model, you could try this alternative version:

Equity (Excluding GL 9160) =
CALCULATE(
    [Equity],
    FILTER(
        ALL(chartofaccounts),
        chartofaccounts[GLnumber] <> "000-9160-00"
    )
)

This uses FILTER(ALL(...)) to make sure the GL account is excluded regardless of slicer context.

Refer this link: https://learn.microsoft.com/en-in/dax/filter-function-dax

Hope this helps. If you have any doubts regarding this, please feel free to ask here. We will be happy to help.

Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Djdjwhao,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @Djdjwhao,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @Djdjwhao,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[Shahid12523]

Your current DAX works for P&L but not for equity because equity measures are on the balance sheet.

Fix: Use a FILTER(ALL(...)) to exclude the GL

CALCULATE(
selectedmeasure(),
FILTER(
ALL(chartofaccounts),
chartofaccounts[GLnumber] <> "000-9160-00"
)
)