cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StidifordN Regular Visitor
Regular Visitor

Re: Sum of values by each category

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.

Capture1.JPG

 

2) date filter applied

Capture2.JPG

 

3) a further filter applied based on a subsequent table (but with correct relationship established)

Capture3.JPG

 

 

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 

StidifordN Regular Visitor
Regular Visitor

Re: Sum of values by each category

I may have just stumbled across a solution to the date filter, 

 

[quote]  

TtlSpendbySupp =

CALCULATE(SUM(APAll[NET_AMOUNT_AU]),

ALLEXCEPT(APAll,APAll[PTRN_SUPP]),

FILTER(ALLSELECTED(APAll[PTRN_POSTED]),

APAll[PTRN_POSTED]<=MAX(APAll[PTRN_POSTED])))

[/quote]

 

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 Smiley Sad

Highlighted
StidifordN Regular Visitor
Regular Visitor

Re: Sum of values by each category

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

Capture4.JPG

 

Image example of it working for a subsequent filter 'Charge Account'

Capture5.JPG

 

 

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?

 

Capture6.JPG