Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

total in report not Adding all values in a table column

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

 

Need Help
Tips and Tricks
  • nilendraFabric's avatar
    nilendraFabric
    1 year ago

    Hello Anonymous 

     

    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
    )

     

     

3 Replies

  • nilendraFabric's avatar
    nilendraFabric
    Icon for Super User rankSuper User
    1 year ago

    Hello Anonymous 

     

    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
    )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi nilendraFabric ,

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

     

    Thanks again,

    Joe

     

     

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Anonymous,
    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.