Relationship Totals

Question

I have a Direct Query model with two tables:&nbsp; Accidents and Incidents.&nbsp; Accidents is joined to the Incidents table by the Incident ID.&nbsp; There are Incident IDs in the Incident table that are not related to accidents.&nbsp; 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.&nbsp; &nbsp;Like the below.&nbsp; It is a Many to One join from Accidents to Incidents.&nbsp; So the questioni is why to I get 154 as a total?&nbsp; When I remove agency name I get two totals.&nbsp; 77 and 154.&nbsp; I thought it would be 77 and 77 due to the Many to One join from Accidents to Incidents.&nbsp; I would expend is Total Incidents to show 154.&nbsp; Thanks in advance.&nbsp;Agency&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Accidents&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IncidentsAgency 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 23Agency 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21Agency 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;33&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 33Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;77&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;154&nbsp;

diki-i · Answer

Can you please attach your pbix file.&nbsp;

anonymous · Answer

Hi&nbsp;Razorbx13&nbsp;,
&nbsp;
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.
&nbsp;
If the above one can't help you get the desired result, please provide some&nbsp;sample data&nbsp;in your tables (exclude&nbsp;sensitive&nbsp;data) with&nbsp;Text&nbsp;format and your&nbsp;expected result&nbsp;with backend logic and special examples.&nbsp;It is better&nbsp;if you can share a&nbsp;simplified&nbsp;pbix file.&nbsp;Thank you.
&nbsp;
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.&nbsp;

Forum Discussion

Relationship Totals

2 Replies

Related content

Relationship

Use relationship total issue

Relationships

Relationship Issue

Creating relationships