Relationship Totals

[Razorbx13]

I have a Direct Query model with two tables:  Accidents and Incidents.  Accidents is joined to the Incidents table by the Incident ID.  There are Incident IDs in the Incident table that are not related to accidents.  When I create a visual showing a list report by Police Agency I see the join "Count" totals by Agency correctly but the overall totals are showing the total Incidents for the whole table, not the total incidents that have a matching ID.   Like the below.  It is a Many to One join from Accidents to Incidents.  So the questioni is why to I get 154 as a total?  When I remove agency name I get two totals.  77 and 154.  I thought it would be 77 and 77 due to the Many to One join from Accidents to Incidents.  I would expend is Total Incidents to show 154.  Thanks in advance.

Agency                    Accidents              Incidents

Agency 1                 23                          23

Agency 2                 21                          21

Agency 3                 33                          33

Total                         77                         154

[v-tangjie-msft]

Hi @Razorbx13 ,

You can create a new measure.

Measure Sum of Distinct Count Incidents = 
SUMX(
    VALUES(Accidents[Agency]),
    CALCULATE(DISTINCTCOUNT(Incidents[Incident ID]))
)

Add this new measure to your visual, and it should display the correct total count of incidents that have a matching ID.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[DiKi-I]

Can you please attach your pbix file.