Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sheant
Regular 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?

 

Thanks in advance.

 

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

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

 

Capture12.GIF

 

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!

View solution in original post

2 REPLIES 2
CheenuSing
Community Champion
Community Champion

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

 

Capture12.GIF

 

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!

Thanks very much CheenuSing.  Works a treat.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.