[Asking Help With 🙏] Calculated Exclusions in Custom Mode

I’m trying to filter out data from a dashboard.

I want to exclude transactions with a specific "Division" (Phocas Property) AND where the Product Margin (Phocas Measure) is less than 20% (in the screenshot below that Measure is called: GM % COGS)

I also need to exclude some other Divisions carte blanche (as in: no matter the PM%, those Divisions should be excluded).

These are the selections I have:
Division Exclusion Filter - Phocas

Where the 1st line has all the ‘carte blanche’ excluded Divisions as Entity exclusions.

And those lines below that in a Calculation is where the problem lies…
in the line for a - It’s taking all transactions of all Divisions where Product Margin is greater than 20%
and then… subtracting all transactions in the Liquidation Divisions (this ‘Liquidation’ selection is the (1) shown in the line for b) where Product Margin is greater 20%

i.e. - it’s showing this calculation only for transactions with a Product Margin% greater than 20 (aka - excluding all transactions in all Divisions with Product Margin% less than 20%)

But I need the selections of those last lines to result in:
All transactions of all Divisions (no considerations for Product Margin%)
and then… subtract any transactions in the Liquidation Division where Product Margin is less than 20%

I can’t seem to only apply that calculation filter of ‘<20’ to just one measure though - it’s applying it to both.

Any solutions would be appreciated.

I’ve ran into similar challenges with Grouping filters in EDA as well. The easiest solution I have found and use often (for more advanced users w/ SQL knowledge) is to create a filtering flag upstream in the dataset, and then using the flag as a filtering mechanism on the report. I’ve listed two possibilities below; from my understanding of your current challenge, I think you could use the first option as long as both division & GM% are in the same dataset and the results are intended to be evaluated on a line by line basis (and not, for example, summarizing the total GM% for the entire division and deriving exclusions based on that logic.)

  1. Database Build - Adding a Transform Column: the Expression freeform entry respects SQL functions, so you can use IIF() and CASE WHEN statements to create the grouping results in a flag of 1 or 0. I use this only when referencing other columns within the dataset which don’t require summing logic; reason being is that EDA evaluates the expression on a line by line basis.

image

  1. Admin - Sync - Create View (requires SQL): When needing to create advanced grouping filters with summing logic, one possibility is to create a similar filtering dataset as the first option, only in this case using the Sync - New View to create the dataset and define your filtering flags. The free form accepts SQL language, so you can get pretty creative with how to apply your filtering flags.

1 Like

I think your solution from option 1 is gonna do the trick - I was trying to avoid an admin/SQL involvement, for the sake of end-user ease, but ya, I just don’t think Phocas is capable of this level calculation on its own front end.