Totals Don't Add Up

[jackj]

I'm struggling with the following result - for some reason, my totals are not adding up:

My dax measures are below:

Total Visits = var t=SUMMARIZE('warehouse','warehouse'[CreatedAt],"UserDistinct",DISTINCTCOUNT('warehouse'[ID] ))return

SUMX(t,[UserDistinct])

Eval = calculate(distinctcount('warehouse'[ID]),filter(ALLSELECTED('warehouse'[Service]),search("Meet",'warehouse '[Service],1,0)>0 || search("Eval",'warehouse'[Service],1,0)>0 || search("M&G",'warehouse'[Service],1,0)>0))

Visits -Eval = [Total Visits] - [Eval]

Any help would be appreciated!  Not sure how this is going wrong, but the totals for Eval are expected to be 48.

Thank you!!

[v-henryk-mstf]

Hi @jackj ,

For this type of error, half are caused by the filter conditions. The allselect function ignores any filters that may be applied to the query, and only keeps the filters from the outside.

Try the following dax formula:

Eval =
CALCULATE (
    DISTINCTCOUNT ( 'warehouse'[ID] ),
    FILTER (
        ALLEXCEPT ( 'warehouse', 'warehouse'[Service] ),
        SEARCH ( "Meet", 'warehouse '[Service], 1, 0 ) > 0
            || SEARCH ( "Eval", 'warehouse'[Service], 1, 0 ) > 0
            || SEARCH ( "M&G", 'warehouse'[Service], 1, 0 ) > 0
    )
)

If the problem is still not resolved, can you provide the relevant test data model for me to do further testing. Looking forward to your reply.

Best Regards,
Henry

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

[Greg_Deckler]

@jackj This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907