cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate a remaining figure

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Calculate a remaining figure

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
2 REPLIES 2
Super Contributor

## Re: Calculate a remaining figure

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Frequent Visitor

## Re: Calculate a remaining figure

Thanks very much CheenuSing.  Works a treat.

Announcements

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 422 members 4,208 guests
Recent signins: