Forum Discussion
Exclude GL account from equity
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"})
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.
7 Replies
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"
)
)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-groupsHope 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.
Hi
It didn't work. I don't know why.
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.
