Wrong total in power bi to columns

[raulopez]

Hi friends!

I have a case in Power BI, that perhaps one of you has happened, I need that in the table type visualization the distitutive count is totaled by ID, the general total is calculated well, but when I add any of the fields of the table as client's office, the totals for sex, office, attention make a distinctive sum different from the total.

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

unique = DISTINCTCOUNT ('TABLE' [ID])

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

[Syndicate_Admin]

I ran the example of @Syndicate_Admin as is and I have the required result, but now I must apply the same for the same column but with a different row level. Example
Rows:
Shop
hours
However the formula applied adds me correctly in the first level but in the 2nd level of row it is distorted.

[v-jayw-msft]

Hi @raulopez ,

It's hard to reproduce the scenario, can you share some sample data in text format instead of screenshot and share the formula you are using?

Best Regards,

Jay

[raulopez]

I attach the PBIX and the dataset, you will be able to observe the difference of the summations with the distittive total.

Download: https://github.com/orlapez/API

[PaulDBrown]

DISTINCTCOUNT returns the count of unique values in the filter context. For the total, you are seeing the count of unique values overall (taking into account any slicers applied). What is it you hope to see?

[raulopez]

My requirement is that the individual sums of each table correspond to the total, as can be seen in each table the total is 5356 when adding Female + Male, FILE + LIBRARY + WAREHOUSE + GYM or Advisory + claim + complain + conference + etc. the total does not coincide with the summations. and then if 5356 is given as a distinctive result in general, the sum should coincide with that total.

Thank you!

[PaulDBrown]

Taking this simple model as an example:

With these two measures...

Distincount Version =
DISTINCTCOUNT ( Sales[Version] )

Distinctcount version SUMX =
SUMX (
    SUMMARIZE (
        'DIM Product',
        'DIM Product'[Product],
        "@count", DISTINCTCOUNT ( Sales[Version] )
    ),
    [@count]
)

you get this:

[raulopez]

Hi PaulDBrown,thanks!

I have only a table , without relationships. 

If you agree, I will share a pbix and the sources with the expected result, it seems that when removing duplicates from power bi, it is not applying it in the correct way.

[PaulDBrown]

Sure! Share the files using a cloud service such as OneDrive, Dropbox, Google Drive etc...

[raulopez]

Download: 

https://github.com/orlapez/API

[Ashish_Mathur]

Hi,

I do not see any mistake anywhere.  Even if you write this measure

=Measure = DISTINCTCOUNT(Example_catered[Cod])

you get the result as 5,356.

[raulopez]

Ashish.  It is that the total is not incorrect, that the measurement is correct, it is about the individual values ​​when they are added, they must coincide with the same number of the total. Try adding them. the problem is applying the distinctive count to the dataset. I need to find the solution to get the distinctive values ​​without having to do it separately per semester.