Division with a Filter

[austinjgreer]

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!

[johnt75]

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.

[austinjgreer]

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.

[johnt75]

Is the column going on the table with the IP addresses or the hostnames. And do both tables have a month column?

[austinjgreer]

The column is going into Table 1. Table 2 is a static list of ~6500 IPs/hostnames that does not change and has no dates on it.

[johnt75]

try

Percentage = DIVIDE( CALCULATE( COUNTROWS('Table1'), 
ALLEXCEPT( 'Table1', 'Table1'[IP Address], 'Table1[Month]) ), COUNTROWS('Table2') )

[austinjgreer]

Tried it, and now I'm getting .02% for all rows. 

[johnt75]

can you provide a sample PBIX ?

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[austinjgreer]

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.