How to CALCULATE a filtered measure with DAX and have the result on every row

[NanardPet]

Hi,

I have one dim table related to one fact table with a one-to-many relationship, and want to calculate a filtered measure with this formula:

Chiffre d'affaires =

CALCULATE(

        'Facts Montants paramétrés'[_Montant D/C],

        'Dim Hiérarchie CSA'[Index]= "70"

)

I receive the good result, but I cannot present it on every row of my table:

I would like to have the same value on each row of the red rectangle.

Can you help me to solve this problem ?

Many thanks in advance

Bernard

[Mikelytics · Accepted solution]

Hi @NanardPet 

Please try the following:

Chiffre d'affaires =
CALCULATE(
        SUM('Facts Montants paramétrés'[_Montant D/C]),
        ALL(Dim Hiérarchie CSA),
        ALL(Facts Montants paramétrés),
        'Dim Hiérarchie CSA'[Index]= "70"
)

or

Chiffre d'affaires =
CALCULATE(
        SUM('Facts Montants paramétrés'[_Montant D/C]),
        ALL(Facts Montants paramétrés),
        'Dim Hiérarchie CSA'[Index]= "70"
)

or

Chiffre d'affaires =
CALCULATE(
        SUM('Facts Montants paramétrés'[_Montant D/C]),
        ALL(Dim Hiérarchie CSA),
        'Dim Hiérarchie CSA'[Index]= "70"
)

If this does not work then please use ALL fucntion on the column Libelle like the following

Chiffre d'affaires =
CALCULATE(
        SUM('Facts Montants paramétrés'[_Montant D/C]),
        ALL('Table of Libelle'[Libelle]),
        'Dim Hiérarchie CSA'[Index]= "70"
)

One other question. Why did you not use an aggregation function on the column _Montant D/C. Have not seen it in this combination before. If there is a specific reason the please take the SUM() function out of my suggestion.

If all of this does not work it would be good to see a picture of your data model as well as fields yuo use in the visual.

Best regards

Michael

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

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

@ me in replies or I'll lose your thread.

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

LinkedIn

[Mikelytics]

@NanardPet  Thank you for your feedback Bernard! Best regards, Michael

[Mikelytics · Accepted solution]

Hi @NanardPet 

Please try the following:

Chiffre d'affaires =
CALCULATE(
        SUM('Facts Montants paramétrés'[_Montant D/C]),
        ALL(Dim Hiérarchie CSA),
        ALL(Facts Montants paramétrés),
        'Dim Hiérarchie CSA'[Index]= "70"
)

or

Chiffre d'affaires =
CALCULATE(
        SUM('Facts Montants paramétrés'[_Montant D/C]),
        ALL(Facts Montants paramétrés),
        'Dim Hiérarchie CSA'[Index]= "70"
)

or

Chiffre d'affaires =
CALCULATE(
        SUM('Facts Montants paramétrés'[_Montant D/C]),
        ALL(Dim Hiérarchie CSA),
        'Dim Hiérarchie CSA'[Index]= "70"
)

If this does not work then please use ALL fucntion on the column Libelle like the following

Chiffre d'affaires =
CALCULATE(
        SUM('Facts Montants paramétrés'[_Montant D/C]),
        ALL('Table of Libelle'[Libelle]),
        'Dim Hiérarchie CSA'[Index]= "70"
)

One other question. Why did you not use an aggregation function on the column _Montant D/C. Have not seen it in this combination before. If there is a specific reason the please take the SUM() function out of my suggestion.

If all of this does not work it would be good to see a picture of your data model as well as fields yuo use in the visual.

Best regards

Michael

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

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

@ me in replies or I'll lose your thread.

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

LinkedIn

[NanardPet · Accepted solution]

The 3d syntax was the good one, due to the content of the table 'Facts Montants paramétrés' in which they are several dates for the same item of 'Dim Hiérarchie CSA'.

Thanks a lot

Bernard