Different results using summarize with filter function and visual level filter

[rfernandes]

Hello everyone,

i'm using a measure with nested summarize to find how many clients are loyal to a product. When i use a dax filter i get a result, when i use the filter in the visual level filter, i get different result . Someone knows what i m doing wrong?

Code with dax filter: 

% Total loyal Clients = 

    COUNTROWS (
        FILTER (
            SUMMARIZE (
                SUMMARIZE (
                    Filter(database; database[year] = 2018);
                    database[id_client];
                    database[product];
                    "total"; SUM ( database[volume] )
                );
                database[id_client];
                database[product];
                "total %"; SUM ( database[volume] )
                    / CALCULATE ( SUM ( database[volume] ); ALL ( database[product] ) )
            );
            [total %] >= 0,75
        )
    ) /DISTINCTCOUNT(database[id_client])
                
     

Code without filter and year 2018 selected as visual level filter:

% Total loyal Clients (visual level filter) = 

    COUNTROWS (
        FILTER (
            SUMMARIZE (
                SUMMARIZE (
                    database;
                    database[id_client];
                    database[product];
                    "total"; SUM ( database[volume] )
                );
                database[id_client];
                database[product];
                "total %"; SUM ( database[volume] )
                    / CALCULATE ( SUM ( database[volume] ); ALL ( database[product] ) )
            );
            [total %] >= 0,75
        )
    ) /DISTINCTCOUNT(database[id_client])
                
     

The two measures above give me distincts results 

I also tried this measure with only one summarize, it gives me the same virtual table(the summarize part), but the measure always return  100%. 

% Total loyal Clients (1 summarize) = 

    COUNTROWS (
        FILTER (
                SUMMARIZE (
                    database;
                    database[id_client];
                    database[product];
                    "total %"; SUM ( database[volume] )
                    / CALCULATE ( SUM ( database[volume] ); ALL ( database[product] )
                );
            [total %] >= 0,75
        )
    ) /DISTINCTCOUNT(database[id_client])
                

 Is it possible to make this measure with summarizecolumns??

here is an example :  example.pbix

Thank you.

[AlB]

Hi @rfernandes 

Can you share the pbix? Otherwise it's kinda hard to see what might be going on

Cheers

[rfernandes]

Hi, i posted a pbix with the example. 

Thank you

[Anonymous · Accepted solution]

Try not to add calcualated columns inside summarize. Try using:

ADDCOLUMNS(
    SUMMARIZE(..
    ),
"Cal Column Name", [MEASURE]
)

[rfernandes]

I tried to use summarize + addcolumns and the results are always 100%.

I was missing a calculate inside de addcolumns measure field.

Thanks

[Anonymous]

Yep, that can happen. ADDColumns is an interator, so you need to use CALCULATE to invoke the context transition, if not you get results that dont make sense, well they do given the code, but not what we would expect. Glad you were able to figure it out

[rfernandes]

Yes, here is the link: ex.pbix

Thank you