Expanding Summary Table

Hi cobus_19,

---

---

My goal is simple, I want to pull all important demographic information from the original table (i have only included agent in the sample) and then be able to see their leads, sales, etc per date, per month and cross filtered vs other demographic information. However, it seems as soon as I add colomns from the original table other than just date I lose more and more accuracy with each column I add - like i'm overfiltering the table before counting.

Instead of a summary table, I would suggest you to create an individual Date table (using CALENDAR or CALENDARAUTO Function (DAX)), and create multiple relationships between the Date table and the "Broker" table with the date column and the "LeadDate", "SaleDate", "QuoteDate", and "AcceptDate" in this scenario. Note: only one relationship can be Active between two tables, others will be Inactive.

For the Active column (for example LeadDate), you can just use the formula below to create a measure to count the account of Leads.

Leads = COUNT ( Broker[leadID] )

For other Inactive columns, you should be able to use USERELATIONSHIP Function (DAX) to create the measure. For example, you can use the formula below to create a measure for Sales.

Sales = 
CALCULATE (
    COUNT ( Broker[leadID] ),
    Broker[SaleDate] <> BLANK (),
    USERELATIONSHIP ( 'Date'[Date], Broker[SaleDate] )
)

Then you should be able to show the measures on the report with Date and other Slicers.

Here is the modified sample pbix file for your reference.:smileyhappy:

Reference: Create and manage relationships in Power BI Desktop

Regards