The dataset is a pretty standard transactional procurement dataset, line item invoice details.
TtlSpendbySupp is the key column with the measure.
1) This is where the formula is working as expected - the whole dataset with no filters applied.
2) date filter applied
3) a further filter applied based on a subsequent table (but with correct relationship established)
The desired outcome is that - any filter applied the TtlSpendSupp measure will also be filtered and result in a 100% allocation on the far right hand side
I may have just stumbled across a solution to the date filter,
And that works perfectly when i slide the date filter around. When i apply another slicer (eg. Charge Account) it fails again.
Is it a matter of listing out all the possible filters that could be applied one after another? Would the MAX function work for non-data/value based data?
EDIT - upon further testing this is not working perfectly
Ok - I have messed around with this for far too many hours but through sheer guesswork I think i have this working.
TtlSpendbySupp = CALCULATE(SUM(APAll[NET_AMOUNT_AU]), ALLEXCEPT(APAll,APAll[SUPPLIER NAME]), FILTER(ALLSELECTED(APAll[PTRN_POSTED]), APAll[PTRN_POSTED]=APAll[PTRN_POSTED]), FILTER(ALLSELECTED(ChargeCode[Charge Account]), ChargeCode[Charge Account]=ChargeCode[Charge Account]))
FYI - the change to supplier name from supplier reference number (PTRN_SUPP) didn't change the functionality - just a cleansing issue with my data.
Image example of it working for the date filter
Image example of it working for a subsequent filter 'Charge Account'
So now the only issue i can see popping up is - when i remove any supplier or charge account filters (only the date filter remains in place but its irrelevant for this problem) why is there infinity against this suppliers figures?