Calculate a remaining figure

Question

Hey guys,&nbsp;I have 2 tables Sites and Site Visits.&nbsp; Each site needs to have a visit per month to meet a KPI.&nbsp; Tables areSiteLocation&nbsp;&nbsp; RegionSite X&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NSWSite Y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NSWSite Z&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QLD&nbsp;Site VisitsVisit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LocationVisit 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Site XVisit 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Site ZVisit 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Site X&nbsp;I would like to create the following table:Region&nbsp;Total Sites&nbsp; Distinct Sites Visited&nbsp;&nbsp; Sites RemainingNSW&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;1QLD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;0&nbsp;I got the Distinct Sites Visited with the following measure:distinct_visit = DISTINCTCOUNT('Site Visits'[location])I just can't seem to get the remaining figure due to the 2 different tables.&nbsp; Does anyone have any sugestions?&nbsp;Thanks in advance.&nbsp;

anonymous · Accepted Answer

Hi sheant&nbsp;Try the following&nbsp;1. Create a measure called&nbsp; TotalSites = DISTINCTCOUNT(Site[Location])2. You already have a measure for distinct_visits3. Create&nbsp; a measure called Balance = [TotalSites] - [distinct-visits]4. Now plot in matrix table.&nbsp;Sample output&nbsp;&nbsp;If this works for you, pleasee accept it as a solution and also give KUDOS.&nbsp;Cheers&nbsp;CheenuSing

Forum Discussion

Calculate a remaining figure

2 Replies

Related content

Calculating active figures

Days Remaining Calculation

Remaining Balance Depends on Prior Month

Need help calculating figures in a column to weekly figures

Calculating desk usage figures