Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago

Missing total distinguished count

Hello friends!

I have a case in Power BI, that maybe some of you have happened, I need that in the table-like visualization the distitive count by ID is totalized, the grand total is calculated well, but when I add any of the fields of the table as the customer's office, the totals by sex,office, attention make a different distinctive sum to the total.

Example with filter in year 2021 and semester 2, with the fields sex, office, attention, when doing the sum by category differs from the distinctive total.

Unique = DISTINCTCOUNT('TABLE'[ID])

There is also a date field that can be repeated in many records.

Example campos.png

8 Replies

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      5 years ago

      My requirement is that the individual summaries of each table correspond to the total, as can be seen in each table the total is 5356 when adding Female+Male, FILE+BIBLIOTECA+BODEGA+GYM or Advisory+claim+complain+conference+etc. does not match the total with the summations. and so if it results in a distinctive result 5356 in general the sum should match that total.

      Thank you!

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        Syndicate_Admin 

        That is the way it works 🙂
        At the total level, it takes all the values and gets the DISINCTCOUNT. It does not add up the other "cells" .
        If you want to get the desired results, you need to add each row as follows. I only did it for office, you will need to create for each category where you need DISTINCTCOUNT.

        Measure = 
IF(
    ISINSCOPE( Example_catered[office]),
    DISTINCTCOUNT(Example_catered[Cod]),
    var __t = ADDCOLUMNS(ALLSELECTED(Example_catered[office]),"@dcount",  CALCULATE(DISTINCTCOUNT(Example_catered[Cod]))) return
    SUMX(__t,[@dcount])     
)