Creating a formula with filters applied

Question

Hi there,&nbsp;&nbsp;I am new to Power BI and I am struggling with something I want to do.&nbsp;&nbsp;I have a data set that shows all the visits in our internal website like in the below screenshot:&nbsp;&nbsp;&nbsp;&nbsp;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.&nbsp;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.&nbsp;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).&nbsp;&nbsp;I managed to get a formula that gives me the total visits:&nbsp;Website Visits= count(Eloqua_PageView[Company])&nbsp;but I don't know how to create a formula that divide this number by the filtered visits of a given team.&nbsp;&nbsp;Since this is the first time I publish anything here please let me know if you need any more data.&nbsp;&nbsp;Thanks in advance,&nbsp;Alessandro&nbsp;&nbsp;&nbsp;&nbsp;

tahreem24 · Answer

Alessandro-laba&nbsp;Welcome to Power BI Community.&nbsp;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&nbsp; = Sum('Table'[VisitNumber])/CALCULATE(sum('Table'[VisitNumber]),ALL('Table'))&nbsp;Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!&nbsp;&nbsp;

alessandro-laba · Answer

Hi there,&nbsp;&nbsp;Yes you are right what I want to do&nbsp;s to divide the specific value to Overall value. However these 2 values come from the same data set.&nbsp;&nbsp;Ideally my formula would be the following: Measure = Divide((Eloqua_PageView[Company]), (Eloqua_PageView[Company])&nbsp;Obviously if I do that the result will be always 1 because I am dividing a number by itself.&nbsp;&nbsp;What i have now is:&nbsp;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&nbsp;count(Eloqua_PageView[Company])Outcome wanted: 69.500 / 4.000.000&nbsp;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.&nbsp;&nbsp;Please let me know if you need anything else.&nbsp;Thanks,&nbsp;Ale&nbsp;&nbsp;

tahreem24 · Answer

Alessandro-laba&nbsp;,&nbsp;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.&nbsp;Please don't forget to hit THUMBS UP and Accept this as a solution if it helps you!&nbsp;

anonymous · Answer

Alessandro-laba&nbsp;Basically you need to get the an overall value and filtered value:&nbsp;&nbsp;overall = countx(all(Eloqua_PageView),Eloqua_PageView[Company])
filtered = count(Eloqua_PageView[Company])&nbsp;For your expected outcome, try divide them.&nbsp;Result = Divide(count(Eloqua_PageView[Company]), counts(all(Eloqua_PageView),Eloqua_PageView[Company]))&nbsp;Best,Paul

alessandro-laba · Answer

Hi Paul,&nbsp;&nbsp;Thank you very much for your reply.&nbsp;&nbsp;Unfortunately it didn't work but I believe this is my fault for creating some missunderstanding.&nbsp;&nbsp;The page views and the department are in 2 different data set as shown below:&nbsp;&nbsp;Eloqua_Pageview data set:&nbsp;&nbsp;&nbsp;Department:&nbsp;&nbsp;These 2 files are connectd by the ID field.&nbsp;&nbsp;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:&nbsp;&nbsp;Visits on the website from Marketing / Total visits on the website = X&nbsp; and then&nbsp;X divided all marketing employees = Outcome I want.&nbsp;&nbsp;Please let me know if this helps you or if you need anything else.&nbsp;&nbsp;Thanks,&nbsp;Ale&nbsp;

Forum Discussion

Creating a formula with filters applied

5 Replies

Related content

If formula

DAX Formula Help

formula

Creating a Date Filter in Power BI

Double filter formula