How to calculate a sum with part of page filters + 1 formula filter

[Dje33]

Good morning,

I need a little help.

I created a report with 3 filters (date / workshop / machine). file attached
My table contains quantities of parts produced by machine and date  (each machine is in a workshop, there are several workshops)

I want to calculate a sum of production quantity and display it in the report with the following filters
- remove the date filter (report page filter)
- keep the workshop report filter (report page filter)
- keep the machine report filter (report page filter)
- add a week filter (formula filter) ("week" data present in the table)

I tried several things without success. I always have either all the filters of the active report or none...

as

the result obtained shows that either 

z_QteProdSem =
VAR ActualWeek = MAXX(all(VtSortiesV2),VtSortiesV2[y_AnSem])
VAR ActualWeekPeriode = FILTER(ALLEXCEPT(VtSortiesV2,VtSortiesV2[y_atelier'],VtSortiesV2[Machine]),VtSortiesV2[y_AnSem]=ActualWeek)
RETURN
SUMX(ActualWeekPeriode, VtSortiesV2[QuantiteTotale])

or

z_QtéSem2 = sumx(FILTER(ALLEXCEPT(Feuil1,Feuil1[Atelier'],Feuil1[Machine]),Feuil1[semaine]=24),Feuil1[QuantiteTotale])

the result obtained shows that either all the filters of the active report or none filter...

Can you help me?

[Dje33]

Hello Sahir,

I found the solution :

I use REMOVEFILTERS and SELECTDVALUE in a formula CALCULATE with variable

VAR FYN1Select=LEFT(SELECTEDVALUE(calendrier[FYannée]),2)&(RIGHT(SELECTEDVALUE(calendrier[FYannée]),2)-1)

RETURN

CALCULATE(SUM(VtSortiesV2[QuantiteTotale]),

 REMOVEFILTERS(calendrier[FYannée]),

 calendrier[FYannée]=FYN1Select)/

CALCULATE(

    SUMX(vevenements,vevenements[Duree]*vevenements[EvenementPLC]*vevenements[y_cadenceM]*60),

    REMOVEFILTERS(calendrier[FYannée]),

    codesEvt[TRS]="oui",

    vevenements[y_FY]=FYN1Select)

[Dje33]

Many thanks Sahir,

Your answer is so simple!

I thought too complicated, I also think I succeeded with variables and CALCULATE

[Sahir_Maharaj]

Hello @Dje33,

To achieve this, you can use a combination of measures and the ALL function to control the context of the calculation:

1. Create a Measure for Week Filter

SelectedWeek = MAX('VtSortiesV2'[y_AnSem])

2. Create a Measure for Calculating Sum

z_QteProdSem = 
VAR SelectedWeek = [SelectedWeek]  -- Use the measure you created in step 1
RETURN
SUMX(
    FILTER(
        ALL('VtSortiesV2'),
        'VtSortiesV2'[y_AnSem] = SelectedWeek &&
        'VtSortiesV2'[y_atelier'] = SELECTEDVALUE('VtSortiesV2'[y_atelier']) &&
        'VtSortiesV2'[Machine] = SELECTEDVALUE('VtSortiesV2'[Machine])
    ),
    'VtSortiesV2'[QuantiteTotale]
)

Should you require further assistance, please do not hesitate to reach out to me.

[Dje33]

Hello Sahir,

In fact there is a problem.

The result is empty. I have the impression that it does not accept to have several machines selected

VtSortiesV2'[Machine] = SELECTEDVALUE('VtSortiesV2'[Machine])