Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
Thanks very much CheenuSing. Works a treat.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |