Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jgulliver
Frequent Visitor

Count the number of agent occurences per listing

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 IDAgent 1Agent 2Listing Date
123456John SmithJane Doe1 Jan 2023
654321John Smith 22 Jan 2023
234567Jane Doe 10 Feb 2023
765432Alex Smith John Smith5 Feb 2023
345678Alex 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

 

 

 

 

 

2 REPLIES 2
danextian
Super User
Super User

Hiu @jgulliver ,

 

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

danextian_0-1676266143240.png

Please see attached pbix for the details.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Jihwan_Kim
Super User
Super User

Hi,

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

 

Jihwan_Kim_0-1676265752205.png

 

 

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.