topic Re: How to calculate a sum with part of page filters + 1 formula filter in DAX Commands and Tips

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

Dje33 — Fri, 11 Aug 2023 15:57:54 GMT

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...

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])

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?

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

Sahir_Maharaj — Sun, 13 Aug 2023 17:48:42 GMT

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.

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

Dje33 — Thu, 17 Aug 2023 14:44:43 GMT

Many thanks Sahir,

Your answer is so simple!

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

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

Dje33 — Fri, 18 Aug 2023 14:49:59 GMT

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])

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

Dje33 — Sun, 20 Aug 2023 12:29:18 GMT

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)