Calculate a remaining figure

[sheant]

Hey guys,

I have 2 tables Sites and Site Visits.  Each site needs to have a visit per month to meet a KPI.  Tables are

Site

Location   Region

Site X        NSW

Site Y        NSW

Site Z        QLD

Site Visits

Visit         Location

Visit 1      Site X

Visit 2      Site Z

Visit 3      Site X

I would like to create the following table:

Region Total Sites  Distinct Sites Visited   Sites Remaining

NSW     2                  1                                    1

QLD      1                  1                                    0

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.  Does anyone have any sugestions?

Thanks in advance.

[CheenuSing · Accepted solution]

Hi @sheant

Try the following

1. Create a measure called  TotalSites = DISTINCTCOUNT(Site[Location])

2. You already have a measure for distinct_visits

3. Create  a measure called Balance = [TotalSites] - [distinct-visits]

4. Now plot in matrix table.

Sample output

If this works for you, pleasee accept it as a solution and also give KUDOS.

Cheers

CheenuSing

[sheant]

Thanks very much CheenuSing.  Works a treat.