Ignore some filters / keep other in calculation

[vsevolozhskiy]

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

[skamath1]

The expression below should ignore the Period selection.

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

[vsevolozhskiy]

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?

[vsevolozhskiy]

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

[vsevolozhskiy]

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

[skamath1]

Can you paste your screenshot of the object.

[vsevolozhskiy]

I' m not sure what do you mean by object

[vsevolozhskiy]

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 

[vsevolozhskiy]

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"

[QFabian]

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 :