Count the number of agent occurences per listing

[jgulliver]

Hi,

Needing some assistance again please. My subject may not be an accurate description of what I am after but the scenario is this.

We are working on a number of metrics and whilst we mostly have the information available, the CRM system we are currently using has a number of dashboard and reporting capabilities, which is why we're having to resort to presenting the data via PowerBI.

I work in the commercial real estate industry and one of the reports that we are wokring on is a report that provide us with the number of active listings we have (unique key is the listing ID). For each listing, we may either have 1 or 2 agents assigned per listing. What we want to get out of this is to provide how many number of listings an agent has on a monthly basis. So for example, if we have this data set...

Listing ID	Agent 1	Agent 2	Listing Date
123456	John Smith	Jane Doe	1 Jan 2023
654321	John Smith		22 Jan 2023
234567	Jane Doe		10 Feb 2023
765432	Alex Smith	John Smith	5 Feb 2023
345678	Alex Smith		6 Feb 2023

For the month of Jan, I would have 2 counts for John Smith and 1 count for Jane Doe.

For the month of Feb, I would have 1 count for Jane Doe, 2 counts for Alex Smith and 1 count for John Smith.

I may need to have a follow-up question but will leave it for later. How do we do this in PowerBI so that the number of listings are reflected accurately for each agent regardless of the agent being Agent 1 or Agent 2?

Thanks,

Joan

[danextian]

Hiu @jgulliver ,

I would unpivot Agent 1 and 2 to be able to calculate the count of listing per agent per month like below:

Please see attached pbix for the details.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Agent count: =
VAR _agentonelist =
    DISTINCT ( Data[Agent 1] )
VAR _agenttwolist =
    DISTINCT ( Data[Agent 2] )
VAR _agentalllist =
    FILTER (
        SELECTCOLUMNS (
            DISTINCT ( UNION ( _agentonelist, _agenttwolist ) ),
            "@agentlist", [Agent 1]
        ),
        [@agentlist] <> BLANK ()
    )
RETURN
    COUNTROWS ( _agentalllist )