Missing total distinguished count

[Syndicate_Admin]

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.

[Fowmy]

@Syndicate_Admin 

So what is your requirement ?

[Syndicate_Admin]

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]

@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])     
)

[Syndicate_Admin]

Yes, it is correct what you are asking me, but if I show a distinctive total result of 5356 which is the distinctive global, the sums of the offices, for example, open me up to 5356.

Because the reference value is that total, if the summation throws 5926 would be exceeding the actual value of the badge which is 5356, it is as if it counts more additional cells, it is loq eu I wish to avoid, otherwise without the distinctiveness and repeated values in the cells there are equalizations the sums. There is a date value for events in the main table, and dates may be repeated, that field type may increase or deter from the total, because dates do not behave as an ID.

[Fowmy]

@Syndicate_Admin 

Based on your requirement either you can go with the default calculation or follow the measure that I shared. It depends on how you want the results to be utilized and how it makes perfect sense 🙂

[Syndicate_Admin]

Thank you very much for your support with the solution, but in both scenarios it is not a logical result, because the one I raised initially do not give the sums with the total, and with the calculation that you kindly shared me exceeds the total. ☹️

In turn, what I want to avoid is having to put a chart or sheet to part for each year-semester, that is not optimal in the visualization.

[Fowmy]

@Syndicate_Admin 

If you can provide me one example with the expected result, even with a simple sample dataset, I will be able to work out a solution. 

[Syndicate_Admin]

Of course, I thank you for your support.

I explain:

I did the job first in the Excel sheet (Example) that file has approximately 29,936 records with duplicates, when I removed the duplicates I noticed that some cells were left with the value Empty Cod in the final columns, I deleted them manually.

The archvio that you will see as semesters in sheets, on each sheet are the separate records in semesters, the union of the records of those 3 semesters: 2020-S1,2020-S2 and 2021-1 in total are 17,321 without duplicates, with that value is that you must work.

I created a new file(Dataset) this is the one that ran out of duplicates 17,321 of the initial 29,936, when filtering in Excel the latter file the summations match.

Here's what the solution should look like:

5399+6565+5357= 17.321

I created a new PBIX file (I named it Dashboard), a sheet with duplicates(29,936) and another sheet without duplicates(17,321) that comes from the DATSET font already filtered and joined with the records of the 3 semesters., when I added the slicer with year and semester to that new sheet, the summations match the total(goal).

conclusion:

In Power Bi when applying the distinctive count measure in the display field cod, or when removing duplicates from table editing, the value it throws from distinctive counting is approximately 14,000, about 3,321 records are lost so it does not work with the first sheet, it should be noted that if the separation is done by semesters and joined as I did in the DATASET(17.321) file everything is added correctly and the target is met.

File download: https://github.com/orlapez/API