total in report not Adding all values in a table column

[joef]

Hi All,

I have a column in my report that shows how many inspections are completed this year, by location.

I have a measure that counts the number of inspections completion for this year:

Completed_Inspections_This_Year =

CALCULATE(

    DISTINCTCOUNT('Asset_Inspections'[Last inspection Date]),

    FILTER(

        'Asset_Inspections',

        YEAR('Asset_Inspections'[Last inspection Date]) = YEAR(TODAY())

    ),

    ALLEXCEPT('Asset_Inspections', 'Asset_Inspections'[Loc])

)

I have the totals value on, and it counts 6 rows, but there are 7 in the table as in the below screenshot

Does anyone know why it would count only 6?  There are clearly 7 in the table 🙂

TIA,

Joe

[joef]

Hi @nilendraFabric ,

Thank you,  removing the ALLEXCEPT and returning the loc did the trick!

Thanks again,

Joe

[v-kpoloju-msft]

Hi @joef,
Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to personally thanks @nilendraFabric, for actively participating in the community forum and his inputs.

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it. Therefore, please refer to the attached pbix file.

I hope this should resolve your issue, if you need any further assistance, feel free to reach out.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Best Regards.

[nilendraFabric · Accepted solution]

Hello @joef 

looks like `DISTINCTCOUNT()` with `ALLEXCEPT()`, is behaving differently in totals.

try this

Completed_Inspections_This_Year =
VAR CurrentYear = YEAR(TODAY())
RETURN
SUMX(
VALUES('Asset_Inspections'[Loc]),
VAR LocationCount = CALCULATE(
DISTINCTCOUNT('Asset_Inspections'[Last inspection Date]),
YEAR('Asset_Inspections'[Last inspection Date]) = CurrentYear
)
RETURN
LocationCount
)