This filter clause do as its own mind

[PatrickByGecko]

Hello
I can't understand why this filter returns information about agency of "Paris" and "Aix" when I'm only asking for "Niort".

CALCULATE(AVERAGE('Affectation Collaborateurs'[TAUX HSITE]);FILTER('Affectation Collaborateurs';'Affectation Collaborateurs'[AGENCE] ="Niort" ))

 

Can somebody explain where I'm wrong?

[SivaMani]

@PatrickByGecko ,

Did you try it as a calculated Measure?

[PatrickByGecko]

I just tried it gives this formula =>

Moyenne de TAUX HSITE pour Niort =

CALCULATE(

    AVERAGE('Affectation Collaborateurs'[TAUX HSITE]);

    'Affectation Collaborateurs'[AGENCE] IN { "Niort" }

)

And it returns always for Aix and Paris too...

[dax]

Hi PatrickByGecko,

You need to use measure instead of calculated column(which will apply result to all table), you could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PatrickByGecko]

I'm sorry but it is a measure (have a look underneath) .

Could you give an example of syntax?

[SivaMani]

@PatrickByGecko,

You're almost there.

Here is the example code,

Average Test = CALCULATE(AVERAGE(Orders[Quantity]), FILTER(Orders,Orders[Ship Mode] = "First Class") )

Please do give a try and let me know!

[PatrickByGecko]

Well done Siva. That works.

But my results need more tuning because I need to calculate average for distinct values of an other columns. For instance in your example Destination;

Quantity                  Ship Mode                   Destination      Year

8                               First Class                    France              2019

3                               First Class                    Usa                   2019

10                             First Class                    France              2020

The Average we saw before will give  (10+3+8)/3 =7

The Average for distinct Destination will give ( (18/2=9) + 10 ) /2 = 8

How can I do that?

🙂

[PatrickByGecko]

Sorry I did a mistake on what I mean =>

----------------------------

Quantity                  Ship Mode                   Destination      Year

8                               First Class                    France              2019

3                               First Class                    Usa                   2019

10                             First Class                    France              2020

The Average we saw before will give  (10+3+8)/3 =7

The Average for distinct Destination will give ( 18 + 3 ) /2 = 10,5

How can I do that?

----------

[SivaMani]

@PatrickByGecko,

Here you go,

DIVIDE(SUM(Orders[Quantity]), DISTINCTCOUNT(Orders[Destination ]))

[PatrickByGecko]

Thanks

But it don't works with a filter =>

Moy Hors Site =

CALCULATE(

    DIVIDE (SUM(CollabAffectImmediat[TAUX HSITE])/DISTINCTCOUNT(CollabAffectImmediat[NOM]);

    FILTER(CollabAffectImmediat;CollabAffectImmediat[DATE DEBUT]<=today() && CollabAffectImmediat[DATE FIN]> TODAY() && CollabAffectImmediat[CODE PROJET]<> "GKOFORMA1" && CollabAffectImmediat[CODE PROJET]<>"GKOMAINT1"&& CollabAffectImmediat[CODE PROJET]<>"GKOAVENTE" )

))

I get this error => L'expression fait référence à plusieurs colonnes. Plusieurs colonnes ne peuvent pas être converties en une valeur scalaire.

[SivaMani]

@PatrickByGecko,

Have you tried this?

Calculate(<exp>,

FILTER(CollabAffectImmediat;CollabAffectImmediat[DATE DEBUT]<=today() && CollabAffectImmediat[DATE FIN]> TODAY()) FILTER(CollabAffectImmediat;CollabAffectImmediat[CODE PROJET]<> "GKOFORMA1" && CollabAffectImmediat[CODE PROJET]<>"GKOMAINT1"&& CollabAffectImmediat[CODE PROJET]<>"GKOAVENTE" ))

[PatrickByGecko]

Hello

I succeeded this way =>

Moy Hors Site =

DIVIDE (

Calculate (

SUM(CollabAffectImmediat[TAUX HSITE]);

    FILTER(CollabAffectImmediat;CollabAffectImmediat[DATE DEBUT]<=today() && CollabAffectImmediat[DATE FIN]> TODAY() )

);

CALCULATE(

DISTINCTCOUNT(CollabAffectImmediat[NOM]);

FILTER(CollabAffectImmediat;CollabAffectImmediat[DATE DEBUT]<=today() && CollabAffectImmediat[DATE FIN]> TODAY() )

)

)

[dax · Accepted solution]

Hi,

So it seems that you solved this problem, right? If so and if you'd like to, you could mark corresponding post as answer or share your solutions. That way, people who in this forum and have similar issue will benefit from it.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.