Need some help with calculated measures with filters

[GoncaloCare]

Hi everyone. 

I need some help when calculating a measure.

So, I have this measure here:

Mat1valor = CALCULATE(SUM(Query3[EUR]), Query3[MAT]="Mat1")

And then, I want to calculate the variation of "Mat2". 

So, I do:

VariacaoMAT1 = ( ( [Mat1valor] / CALCULATE(SUM(Query3[EUR]), Query3[MAT]="Mat2" ) - 1 ))

And then, I select the filter in the filter menu (I do this to apply MAT1 to other graphs):

And, especially the second measure works well even though I'm selecting "Mat1" filter.

But, I have another 2 measures that I do:

NVisitasMat1 = SUMX (
    SUMMARIZE ( Filter(Query3, Query3[MAT]="Mat1") , Query3[id_fatura_unico], Query3[Contagem] , "Total Average", Query3[Contagem] ),
    [Total Average]
)

and then the variation:

VariacaoNvisitasMat1 = ( ( [NVisitasMat1] / SUMX (
    SUMMARIZE ( Filter(Query3, Query3[MAT]="Mat2") , Query3[id_fatura_unico], Query3[Contagem] , "Total Average", Query3[Contagem] ),
    [Total Average]
) ) - 1 )

The problem is that, in this case, the variation gives me "Infinity" because the sumx is 0 because I have the filter in "Mat1" and the filter on the measure is "Mat2". The strange thing is that on the variation above, everything works fine.

How can I make the "VariacaoNvisitasMat1" measure "forget" the selected filter?

Hope you can help me! Best regards!

[GoncaloCare]

Hi.

I forgot to put a link. Here is a link with fake data. 

What I want is, if I select "MAT1" on filters, I want the right value to work properly and not show "Blank".

https://www.dropbox.com/s/0tq98cozqtlpjql/testev2.pbix?dl=0

[Icey · Accepted solution]

Hi @GoncaloCare ,

VariacaoMAT1 =
(
    (
        [Mat1valor]
            / CALCULATE ( SUM ( Query3[EUR] ), Query3[MAT] = "Mat2" ) - 1
    )
)

VariacaoMAT1 - 2 =
(
    (
        [Mat1valor]
            / CALCULATE (
                SUM ( Query3[EUR] ),
                FILTER ( ALL ( Query3[MAT] ), Query3[MAT] = "Mat2" )
            ) - 1
    )
)

Since [VariacaoMAT1] works the same as [VariacaoMAT1 - 2], try to add "ALL(Query3[MAT])" at the end.

VariacaoNvisitasMat1 =
[NVisitasMat1]
    / CALCULATE (
        SUMX (
            SUMMARIZE (
                FILTER ( Query3, Query3[MAT] = "Mat2" ),
                Query3[id_fatura_unico],
                Query3[Contagem],
                "Total Average", Query3[Contagem]
            ),
            [Total Average]
        ),
        ALL ( Query3[MAT] )
    ) - 1

Best Regards,

Icey

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

[amitchandak]

@GoncaloCare , Without filter function filter in calculate, is same as a filter with all

try like

VariacaoNvisitasMat1 = ( ( [NVisitasMat1] / SUMX (
    SUMMARIZE ( Filter(All(Query3), Query3[MAT]="Mat2") , Query3[id_fatura_unico], Query3[Contagem] , "Total Average", Query3[Contagem] ),
    [Total Average]
) ) - 1 )

Check this - http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/

[GoncaloCare]

Hi @amitchandak 

Thanks for the reply. 

I already tried this and it didn't work. And it was very strange.

To make a comparison, I remove the filters, and I have these two measures:

NVisitasMat2 = SUMX (
    SUMMARIZE ( Filter(Query3, Query3[MAT]="Mat2") , Query3[id_fatura_unico], Query3[Contagem] , "Total Average", Query3[Contagem] ),
    [Total Average]
)

NVisitasMat2v2 = SUMX (
    SUMMARIZE ( Filter(all(Query3), Query3[MAT]="Mat2") , Query3[id_fatura_unico], Query3[Contagem] , "Total Average", Query3[Contagem] ),
    [Total Average]
)

As you can see, the only difference is the "All(query3)". 

But the results are different (the correct is the left one):

What can be causing this?