sum() ignoring filters

[charlie2]

Hi,

I have a data set with sales per sales rep (RSM), year, and article code, in one app.

I would like to set up a KPI which shows the total sales of a particular RSM and a particular year. Apparently, it seems an easy job: filter by RSM and filter by year. However, I would like to still show the total sales of the selected RSM and selected year, even when I have filtered by a specific article code.

I know I could do it through the alternative states. Nevertheless, I need these for other purposes.

I have tried the following formula for the KPI, but I, unfortunately, do not manage to get the desired result:

Formula:

sum({<Year= {$(=Max(Year))}, [RSM] = {$(=GetFieldSelections[RSM])}>} Sales)

Result:

sum({<Year= {2021}, [RSM] = {}>} Sales)

I would appreciate if anyone knows how to solve this problem.

[agigliotti]

Hi @charlie2 ,

To me it's not clear what you are trying to achieve.

What do you mean with "filter a specific article code" ?

which are your  fields selections in that scenario ?

what's the actual result and the expected one?

Best Regards

[charlie2]

Hi @agigliotti 

Thanks for your quick reply!

So my possible filters are: Article Code, RSM and Year. I would like to use these three filters, but in the KPI only show the sum of Sales from the filtered RSM and Year, therefore ignoring the filter from the Article Code.

At the moment, my result takes the sum of Sales of the filtered Article Code, RSM and Year.

Best regards,

[agigliotti]

You could use the below expression to ignore the Article Code field selections:

sum( {< [Article Code] >} Sales )

I hope it can helps.

Best Regards

[charlie2 · Accepted solution]

Hi @agigliotti ,

Thanks for your proposal. Unfortunately it doesn't work but I found the following solution, which it seems to deliver the right figures:

sum({1<Year= {$(=Max(Year))}, [RSM] = {'$(=GetFieldSelections([RSM]))'}>} Sales)

Best,