cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JJenkins2005 Frequent Visitor
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
v-xjiin-msft Super Contributor
Super Contributor

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

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] )
)

1.PNG

 

Thanks,
Xi Jin.

5 REPLIES 5
Super User
Super User

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

Hi @JJenkins2005,

 

Can you share some sample data and expected result?

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




JJenkins2005 Frequent Visitor
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. 

EXEC dbo.usp__SSRS__GetLoginDetails @p_LocationCode ='%';

Raw Data.png

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

 

Location Info.png

 

 

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. 

Highlighted
v-xjiin-msft Super Contributor
Super Contributor

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

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 Frequent Visitor
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

WAS49
VAL4
TUC5
TOR10
TCA9
TAM48
STT10
STL36
STA10
SFO53
SEA11
SDO25
SDD2
  

 

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 Super Contributor
Super Contributor

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

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] )
)

1.PNG

 

Thanks,
Xi Jin.

Helpful resources

Announcements
Virtual Launch Event

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 253 members 2,792 guests
Please welcome our newest community members: