Creating a formula with filters applied

[Alessandro-laba]

Hi there, 

I am new to Power BI and I am struggling with something I want to do. 

I have a data set that shows all the visits in our internal website like in the below screenshot: 

 

On another file I have a data set that shows all the personal information of our employees and in which department they are. These 2 files are linked by their contact ID.

What I am trying to do is showing the level of engagement that any team has with our internal website. In theory what I would like to have is a graph that shows me the level of engagement of a team when I select that team from the fitler vision.

To do that the first thing I need to to is divide the visits to our website for a given team by the total visits of our website. The problem here is that both criteria come from the same data set (the above screenshot). 

I managed to get a formula that gives me the total visits: Website Visits= count(Eloqua_PageView[Company]) but I don't know how to create a formula that divide this number by the filtered visits of a given team. 

Since this is the first time I publish anything here please let me know if you need any more data. 

Thanks in advance, 

Alessandro

[Tahreem24]

@Alessandro-laba Welcome to Power BI Community.

What I understood from your post is to divide the specific value to Overall value. Is this right? If yes, then try below formula:

Measure  = Sum('Table'[VisitNumber])/CALCULATE(sum('Table'[VisitNumber]),ALL('Table'))

Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!

[Alessandro-laba]

Hi there, 

Yes you are right what I want to do s to divide the specific value to Overall value. However these 2 values come from the same data set. 

Ideally my formula would be the following: Measure = Divide((Eloqua_PageView[Company]), (Eloqua_PageView[Company])

Obviously if I do that the result will be always 1 because I am dividing a number by itself. 

What i have now is:

Overall visits on internal website : 4.000.000 from count(Eloqua_PageView[Company])

Visits to the internal website from Marketing team when the filter is applied: 69.500 from count(Eloqua_PageView[Company])

Outcome wanted: 69.500 / 4.000.000

The problem is that when i select the Filter the 2 numbers automatically become the same so i am looking for a way to only affect one number when i select a filter. 

Please let me know if you need anything else.

Thanks, 

Ale

[V-pazhen-msft]

@Alessandro-laba 

Basically you need to get the an overall value and filtered value: 

overall = countx(all(Eloqua_PageView),Eloqua_PageView[Company])
filtered = count(Eloqua_PageView[Company])

For your expected outcome, try divide them.

Result = Divide(count(Eloqua_PageView[Company]), counts(all(Eloqua_PageView),Eloqua_PageView[Company]))

Best,
Paul

[Alessandro-laba]

Hi Paul, 

Thank you very much for your reply. 

Unfortunately it didn't work but I believe this is my fault for creating some missunderstanding. 

The page views and the department are in 2 different data set as shown below: 

Eloqua_Pageview data set: 

Department: 

These 2 files are connectd by the ID field. 

In my Power BI dashboard the 'OracleDeparment' is a filter so when i select that filter and select Marketing the formula should do the following calculation: 

Visits on the website from Marketing / Total visits on the website = X  and then 

X divided all marketing employees = Outcome I want. 

Please let me know if this helps you or if you need anything else. 

Thanks, 

Ale 

[Tahreem24]

@Alessandro-laba ,

So try that formula which I've given. If possible can you please share sample data of your file. Or just create a demo excel file which will have that fields so that I can help you out.

Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!