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.

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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi bdoucet,

 

Share the link from where i can download your file.

  • bdoucet's avatar
    bdoucet
    Regular Visitor
    8 years ago

    Hi Ashish,

     

    Excuse me for being ignorant, but I'm not sure how to attach a link to the file. The file is saved on my local desktop and it doesn't appear that I can attach it through the link in the forum. How can I get the file to you for review?

    • bdoucet's avatar
      bdoucet
      Regular Visitor
      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.