topic Re: Ignore some filters / keep other in calculation in App Development

Ignore some filters / keep other in calculation

vsevolozhskiy — Fri, 02 Apr 2021 08:43:02 GMT

Dear all,

please support.

I have following fields: Year, Period, Factory, KPI, Value

Fields: Year, Period, Factory are set up as filters in Application

I need to calculate Sum(Value); however KPI has to be fixed; e.g. KPI={'Revenue'}, sum has to be responsive for Year and Factory but Ignore Period

Keeping "Revenue" I understand: sum({$<[KPI]={'Revenue'}>}[Value]), but how to adjust Formula so it ignores filter for Period?

MANY-MANY THANKS in Advance

Sergey

Re: Ignore some filters / keep other in calculation

skamath1 — Thu, 12 Nov 2020 18:00:00 GMT

The expression below should ignore the Period selection.

sum({$<[Period]=, [KPI]={'Revenue'}>}[Value])

Re: Ignore some filters / keep other in calculation

vsevolozhskiy — Thu, 12 Nov 2020 18:07:24 GMT

it did not help, i use:

sum({$<[Period]=, [KPI]={'TotalNewOrders'},[Status]={'Actual'}>}[Value])

but SUM is still responsive to Filter Pane "Period", any other ideas?

Re: Ignore some filters / keep other in calculation

vsevolozhskiy — Thu, 12 Nov 2020 18:08:28 GMT

"Status" is another Filed, but I believe it plays no role to Formula concept

Re: Ignore some filters / keep other in calculation

vsevolozhskiy — Thu, 12 Nov 2020 18:11:23 GMT

Follow up Problem is Pivot Table, Demission has to be Period 1-12, but it has to be fixed and ignore selection of "Period" in Filter Pane

Re: Ignore some filters / keep other in calculation

skamath1 — Thu, 12 Nov 2020 18:28:26 GMT

Can you paste your screenshot of the object.

Re: Ignore some filters / keep other in calculation

vsevolozhskiy — Thu, 12 Nov 2020 18:34:15 GMT

I' m not sure what do you mean by object

Re: Ignore some filters / keep other in calculation

vsevolozhskiy — Thu, 12 Nov 2020 18:35:45 GMT

you can see First Printscreen w/o Period chosen, Second is with period chosen und Sum is different, however it has to stay the same

Re: Ignore some filters / keep other in calculation

vsevolozhskiy — Thu, 12 Nov 2020 18:40:43 GMT

the same Task for Pivot Table

it has to have always 12 Periods as dimension

on the second printscrren it has only one dimension "4" as Filter for Period is chosen "4"

Re: Ignore some filters / keep other in calculation

QFabian — Thu, 12 Nov 2020 19:13:27 GMT

Hi @vsevolozhskiy try this sample data :

LOAD * INLINE [
Period, FY, ammount
1, 2020,564
2, 2020,56
3, 2020,456
4, 2020,456
5, 2020,456
6, 2020,765
7, 2020,46
8, 2020,890
9, 2020,6345
10, 2020,8
11, 2020,7
12, 2020,564
1, 2019,564
2, 2019,75
3, 2019,345
4, 2019,9676
5, 2019,5673
6, 2019,32423
7, 2019,645645
8, 2019,2342
9, 2019,543
10, 2019,576
11, 2019,789
12, 2019,987
];

then i created a chart whit 'Period' as dimension and the following expression :

sum({<Period = >} ammount)

and it works!, so you have to check the others 3 selected fields :

Re: Ignore some filters / keep other in calculation

vsevolozhskiy — Thu, 12 Nov 2020 19:33:37 GMT

tnx,

of course it works, it only ignores "Period" - it is much more simpler

but it my case it has to Ignore "period" , be responsive for [FY] and [Factory] and force only one KPI [KPI]={'TotalNewOrders'} and only one Status [Status]={'Actual'}

what about PIVOT? at least in your case? I need to have Dimension "Period" o show always period 1-12 regardless of filter

Re: Ignore some filters / keep other in calculation

vsevolozhskiy — Thu, 12 Nov 2020 19:34:27 GMT

can we talk via Teams? Skype?

Re: Ignore some filters / keep other in calculation

vsevolozhskiy — Thu, 12 Nov 2020 20:01:38 GMT

Thanks for support, BUT

it is not the same, yours is much more simpler, of course it works.

I need to ignore "Period" in filter, respond to Year and Factory in Filter and keep KPI and Status to fixed value