Visual total not matching sum of individual rows when filtering

[Andvil]

Good afternoon community,

I have a report that includes sales codes and I need my visuals to show the distinct count of the sales codes. I used to filter by month and then realized than in the distinct count formula I need to force a row by row context, as whenever I applied the month filter the visual distinct count total didn´t match the row by row sum.

I applied this formula and when filtering by month, the row by row sum was equal to the distinct count total: 

SumX Measure = 
SUMX(
    VALUES( 'Dates Table'[YearMonthnumber] ),
    CALCULATE(
        DISTINCTCOUNT( '2020'[Sales Code] )
    )
)

However, now I need to use other filters rather than only the month, but when I filter according to  other variables, again I have the problem that the distinct count total is not matching the row by row sum. I need the visual to have the correct distinct count total (matching with the row by row sum) by selecting different filters.

I attach an example so you can better understand what I need to do, my file includes the filters I usually apply and other dax formulas I have tried: Distinct Count Error 

I previously posted this example to ask for help to filter by month and have the correct grand total. My post´s title is "visual total not matching sum of individual row ", now I need further help to apply other filters besides the month.

I would really appreciate your answers and help.

Best wishes,

Jalv

[v-shex-msft]

HI @Andvil ,

It seems your formula only considers the filter effect which you defined in values function. If you want to keep other filter effects, You can try to use allselected instead: (notice: please double check the relationships key between the current table and that table if filter works on other table fields)

SumX Measure = 
SUMX(
    ALLSELECTED( 'Dates Table'),
    CALCULATE(
        DISTINCTCOUNT( '2020'[Sales Code] )
    )
)

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT 

Regards,

Xiaoxin Sheng

[Andvil]

Hi @v-shex-msft ,

I tried that formula and did not work. It is the column with the title All Selected. Regarding the relationships, the dates table filters the 2020 table.

 

Even when I open more detail, the whole column has the 210 value.

Best,

JALV

[v-shex-msft]

Hi @Andvil,

Please share some dummy data with raw table structure and relationships, it is hard to do further test without sample data.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@Andvil , you might have to replicate the thing for another group by too, in case you need sum.

[Andvil]

Hi @amitchandak  can you explain me please the group by?

[Andvil]

Hi @amitchandak 

I did not understand, how do I replicate for another group?