Forum Discussion

austinjgreer's avatar
austinjgreer
Frequent Visitor
4 years ago

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 (Tab...
Need Help
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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's avatar
austinjgreer
Frequent Visitor
4 years ago

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's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

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

    • austinjgreer's avatar
      austinjgreer
      Frequent Visitor
      4 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        try

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