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