Forum Discussion
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
Hi v-ljerr-msft
I apologize for the delayed response.
My problem is this: (the only difference between the 2 is that one is summarized on date only and one is summarized by adding the column "agent")
As you can see, the leads are correct using the formula:
"Leads", COUNTX( FILTER( FILTER(Broker,Broker[Company]="RBS Car"), Broker[LeadDate]=EARLIER(Broker[LeadDate]) && Broker[Agent]=EARLIER(Broker[Agent]) ), Broker[LeadDate]
However, some sales go missing. The formula is exactly the same except for reference to SaleDate instead of LeadDate
"Sales", COUNTX( FILTER( FILTER(Broker,Broker[Company]="RBS Car"), Broker[SaleDate]=EARLIER(Broker[LeadDate]) && Broker[Agent]=EARLIER(Broker[Agent]) ), Broker[SaleDate]
I'm working on setting up some sample data if this still doesn't help.
Regards
Cobus
Hi v-ljerr-msft
Here is the pbix, I have already set out the 3 approaches:
Original - This is the data as pulled from SQL with cleaned up date fields
WithAgent - This is my summary pulling in Agent that doesn't work
NoAgent with SalesSummary - for this I created two tables, a summary on LeadDate and then a summary on SaleDate which I then link. This seems to give the best results as the other 2 tables are both incorrect as soon as you pull into more detail. However with this approach I am unable to cross filter, for example if I want to select an agent and see how many leads he got over time or something like that.
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.
I hope this makes sense
Regards
Cobus
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
Hi v-ljerr-msft
I have actually created a date table exactly for this purpose, I just had no idea I could use inactive relationships using that function.
This sounds like it will solve my problem, thank you very much!
I will test it in the coming days and let you know how it pans out!Thanks again
Regards
Cobus