Forum Discussion

bdoucet's avatar
bdoucet
Regular Visitor
8 years ago
Solved

CountIf with Group by in Power Query

Hello,    I've seen a few threads on this but cannot find an answer for my problem    I have a table of visits and each row is a unique visit. Currently it's displaying all of the detail for each...
  • bdoucet's avatar
    bdoucet
    8 years ago

    I found a solution. 

     

    Before aggregating, I added a custom column with a formula like:

    = Table.AddColumn(#”Reordered Columns”, “IsVerified”, each if [VisitStatus] = “Verified” then 1 else 0)

     

    Then when grouping, I added another column to the grouping that made a sum of the custom column.

     

    Appears to be a simple solution that's working quite well. 

     

    Thanks to everyone for their suggestions.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi bdoucet,

 

Share some data and show the expected result.

bdoucet's avatar
bdoucet
Regular Visitor
8 years ago

Thank you Ashish.

 

The first screenshot is the raw data. It's a daily visit file with the VisitID being unique so each row is a single visit. The end result should be a group by DepartmentID and Visit date, with a col

 

 

The goal is to group the data by department then visit date, with a column that shows total visits (count of all rows), then two custom columns. See example below. 

 

1.) Count of rows where VisitStatus = "Verified"

2.) Count of rows where CoverageVRFStatus = "E-Verified"

 

 

 

 

 

Afterwards, I'm giong to create two measures in PowerPivot that divides these two new columns by total visits to get:

1.) Percentage of Visits that were Verified

2.) Percentage of Visits where a coverage was E-Verified

 

Which would be: SUM[Total Visits]

 

then: DIVIDE [Total Visits],[Verified Visits] etc.