cancel
Showing results for
Did you mean:
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

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

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.

5 REPLIES 5
Highlighted
Super User

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

Can you share some sample data and expected result?

Regards,

MFelix

Proud to be a Datanaut!

Frequent Visitor

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

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.

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.

Super Contributor

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

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.

Frequent Visitor

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

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....

Super Contributor

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

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.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 44 members 1,284 guests
Recent signins: