How to get percent from Sum of Distinct name in Table one Divided by total in table 2

[JJenkins2005]

Hello,

I'm looking to calculate the percent based on a filtered value in Table One and the same filtered value in table 2. 

Table  One contains all users and the number of times they logged into an application via their desktop and via a mobile device.   This leads to a single person potentially having multiple logins via each platform each day.  We are only interested in if they logged in via mobile or desktop at any given point in a single week. 

Table two contains the number of users broken down by their physical location. 

What I'm looking to do is based on the slicer that is set on the report for the location is calculate the percentage of users who logged in via their desktop and mobile each week. 

I can't figure out the best way to calculate this in PowerBI. 

[MFelix]

Hi @JJenkins2005,

Can you share some sample data and expected result?

Regards,

MFelix

[JJenkins2005]

Sure thing...   I have two stored procedures that provide me with the datasets:

1 - User's assigned location and if the application was opened locally or remotely and when. 

EXEC dbo.usp__SSRS__GetLoginDetails @p_LocationCode ='%';

2.  Each location within the company and the total number of users at each location.

DECLARE @return_value int

EXEC @return_value = [dbo].[usp__SSRS__GetLocationCounts]

SELECT 'Return Value' = @return_value

What I'm looking to do is have a table and a chart visual that will show  the following:

Week # 3 , Site XYZ   Number of Users local 23  Number of users remote 10    Percent Local = 23/total users from query two...  Percent Remote =10/total users from query two.

Hopefully, this makes sense. 

[v-xjiin-msft]

Hi @JJenkins2005,

I got several questions:

1. Week # 3. Did you mean column [DateWeek] = 3?

2. Site XYZ. Did you mean column [PhysicalLocation] = "XYZ"?

3. Is the relationship between the two table column [AssignedLocation] = column [PhysicalLocation]?

4. Is Total User = the value in column [Employee Count].

Since you have already shared the sample data. Why don't you explain your logic based on this sample data? Also, please kindly share us the sample data which we can copy and paste directly.

By the way, I have made a sample based on my assumption. First I created a relationship between the two tables on column [AssignedLocation] and column [PhysicalLocation]. Then created measures to calculate the percentage.

Percent Local =
DIVIDE (
    CALCULATE (
        COUNT ( Assigned[DateGenerated] ),
        FILTER ( Assigned, Assigned[OnPreimise] = "Local" ),
        ALLEXCEPT ( Assigned, Assigned[AssignedLocation], Assigned[DateWeek] )
    ),
    MAX ( Location[Employee Count] )
)

Same to Percent Remote, just replace Local to Remote. And you can check the sample report below. Hope it helps.

https://1drv.ms/u/s!AlqSnZZUVHmshWgz6YKiY10IyPJa

Thanks,
Xi Jin.

[JJenkins2005]

1. Yes, I was referring to the information in [DateWeek]. 

2. Yes,  [PhysicalLocation]

3. Yes, I have a relationship with assigned location linked to physical location

4. Yes, correct. 

OCC Data Table

AssignedLocation DateGenerated DateWeek OnPremise
PBG 1/20/2018 0:00 3 Remote
STL 1/20/2018 0:00 3 Remote
ATL 1/24/2018 0:00 4 Remote
BOS 1/22/2018 0:00 4 Remote
CHS 1/25/2018 0:00 4 Remote
CLT 1/27/2018 0:00 4 Local
CMD 1/25/2018 0:00 4 Remote
COL 1/25/2018 0:00 4 Local
DAL 1/25/2018 0:00 4 Remote
DEN 1/24/2018 0:00 4 Remote
FSD 1/27/2018 0:00 4 Remote
HOU 1/23/2018 0:00 4 Local
HOU 1/25/2018 0:00 4 Remote
LAF 1/27/2018 0:00 4 Remote
LAS 1/27/2018 0:00 4 Local
LAX 1/24/2018 0:00 4 Remote
MEM 1/27/2018 0:00 4 Local
MIL 1/23/2018 0:00 4 Remote
NAS 1/25/2018 0:00 4 Remote
NYC 1/25/2018 0:00 4 Remote
OKL 1/22/2018 0:00 4 Remote
OKL 1/27/2018 0:00 4 Remote
ORA 1/25/2018 0:00 4 Remote
PAR 1/25/2018 0:00 4 Remote
PIT 1/27/2018 0:00 4 Local
POR 1/25/2018 0:00 4 Remote
RAL 1/25/2018 0:00 4 Remote
REN 1/26/2018 0:00 4 Remote
RIC 1/25/2018 0:00 4 Remote
SAC 1/24/2018 0:00 4 Local
SAC 1/26/2018 0:00 4 Remote
SAN 1/25/2018 0:00 4 Remote
SDD 1/27/2018 0:00 4 Local
SDO 1/22/2018 0:00 4 Local

Employee Count Table

PhysicalLocationEmployee Count

WAS	49
VAL	4
TUC	5
TOR	10
TCA	9
TAM	48
STT	10
STL	36
STA	10
SFO	53
SEA	11
SDO	25
SDD	2

So here is the logic for what I'm looking to create in a matrix and a chart

Week 4  SDO  Local = 100% (2 distenct logins/2 (employee count))  Remote 0% ( (0 distinct logins/2 (employee count))

Week 3 STL    Local = 0%(0 distenct logins/36 (employee count)),  Remote 0.27% (1 distinct login/36 (employee count))

Hopefully this makes better sense.... 

[v-xjiin-msft · Accepted solution]

Hi @JJenkins2005,

Have you tried my solution above? I think it should work for you.

Then for your new sample data. There's only 1 distinct local login for SDO and the employee count is 25. So the percentage should be 1/25 = 4%. Right? Please verify your sample data.

And here's the new expression based on your new sample data. Basically same as above solution.

Percent Local = 
DIVIDE (
    CALCULATE (
        COUNT ( 'OCC Data Table'[DateGenerated] ),
        FILTER ( 'OCC Data Table', 'OCC Data Table'[OnPremise] = "Local" ),
        ALLEXCEPT ( 'OCC Data Table', 'OCC Data Table'[AssignedLocation], 'OCC Data Table'[DateWeek] )
    ),
    MAX ( 'Employee Count Table'[Employee Count] )
)

Percent Remote = 
DIVIDE (
    CALCULATE (
        COUNT ( 'OCC Data Table'[DateGenerated] ),
        FILTER ( 'OCC Data Table', 'OCC Data Table'[OnPremise] = "Remote" ),
        ALLEXCEPT ( 'OCC Data Table', 'OCC Data Table'[AssignedLocation], 'OCC Data Table'[DateWeek] )
    ),
    MAX ( 'Employee Count Table'[Employee Count] )
)

Thanks,
Xi Jin.