Count and divide based on condition for MTD table

Create three new queries within Power Query:

First, a new query (let's call it 'Query1') which references your original table and removes all columns apart from the Vertical and Account columns. Then use the Group By feature, Advanced, selecting the Vertical and Account columns and selecting Count Rows as the operation for the new column.

Second, a new query (let's call it 'Query2') which is identical to the above though which contains an additional step prior to the Group By step in which the Status column is filtered for 'Yes' only.

Finally, a new query which performs two merges:

1) A Left-Outer merge with your original table and Query1, using the Vertical and Account columns. Then expand this table, selecting the Count column only

2) A Left-Outer merge with the table generated in 1) above and Query2, using the Vertical and Account columns. Then expand this table, selecting the Count column only.

Finally, add a new custom column to this table which performs a simple division of the relevant columns.