Forum Discussion
Division with a Filter
Hello,
I'm trying to divide using the count of values in one column, by the count of values in another column within a different table.
What I had originally was: Scan Rate = DIVIDE (COUNT (Table1[IP Address]), COUNT ('Table2'[Hostname]))
and that worked great. But after combining files, I now want to do the same division but with a filter (if possible).
I have 2 columns:
IP Address Month Reported
1.2.3.4 March 2022
1.2.3.5 February 2022
Originally, with the one column (IP Address), I was getting the correct percentage. But after combining files it comes to an inflated percentage because it's counting ALL cells within the column, regardless of what month is in the Month Reported column.
So if I could get the following:
IP Address Month Reported Percentage
1.2.3.4 March 2022 98%
1.2.3.5 February 2022 93%
Hopefully this makes sense. Thank you in advance for the assistance!
9 Replies
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Hi! The sample data is above in the OP but I'll put it below too:
IP Address Month Reported
1.2.3.4 March 2022
1.2.3.5 February 2022
1.2.3.6 November 2021
The desired output is:
IP Address Month Reported Percentage
1.2.3.4 March 2022 98%
1.2.3.5 February 2022 93%
1.2.3.6 November 2021 96%
What I have now is:
Percentage = DIVIDE (COUNT(Table 1[IP Address]), COUNT('Table 2'[IP Address]))
So I'm counting the number of IPs in Table 1 and dividing it by the count of IPs in Table 2. But it's counting all IPs in Table 1 regardless of the month listed in the Month Reported column. Hopefully this is clearer.
Create a date table linked to both Table1 and Table2. Use the year / month column from the date table on your visual and your existing measure should work fine.
Apologies if I'm misunderstanding, I'm still fairly new to PowerBI.
This is all still in the Data tab and it is a column that I'm creating, not a measure. Is the auto date/time setting not sufficient for this?
The result I'm getting is 532% in the Percentage column because it is counting ALL IPs in Table 1 and dividing it by the count of IPs in Table 2.
Is the column going on the table with the IP addresses or the hostnames. And do both tables have a month column?
