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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cobus_19
Frequent Visitor

Expanding Summary Table

With some help on a previous topic I was able to create a daily summary table with the formula: (I hope the formatting is ok, still new to DAX - feel free to comment if it's off)

DailySummaryTable = 
	ADDCOLUMNS(
		SUMMARIZE(
			Broker,
			Broker[LeadDate]
		),
		"Leads", COUNTX(
			FILTER(
				Broker,
				Broker[LeadDate]=EARLIER(Broker[LeadDate])  
			),
			Broker[LeadDate]
		),
		"Sales", COUNTX(
			FILTER(
				Broker,
				Broker[SaleDate]=EARLIER(Broker[LeadDate]) 
			),
			Broker[SaleDate]
		)
)


This gives me a great daily summary, however how would I set about including some other columns from my main table?
For example, what if I want to see a breakdown of [category1] per day. How do I introduce this without breaking the daily counts?
 
So my current table output is

Origin Date | Status 1 Count | Status 2 Count | etc
date1 | 10 | 20
date2 | 5| 30
etc
 
My desired output is:
Origin Date | Category | Status 1 Count | Status 2 Count
date1 | category1 | 5 | 15
date1 | category2 | 5 | 5
date2 | category1 | 1 | 10
date2 | category2 | 2 | 10
date2 | category3 | 3 | 10

My problem is that if I simply add another column to my SUMMARIZE statement then it assigns the daily value to each item in that new column. And if I add an additional filter to my COUNTX statement the numbers don't quite add up. My attempted code that doesn't work correct is:

DailySummaryTable = 
	ADDCOLUMNS(
		SUMMARIZE(
			Broker,
			Broker[LeadDate],
                        Broker[category1]
		),
		"Leads", COUNTX(
			FILTER(
				Broker,
				Broker[LeadDate]=EARLIER(Broker[LeadDate]) &&
                                Broker[category1]=EARLIER(Broker[category1]) 
			),
			Broker[LeadDate]
		)
         )


 
Your help is much appreciated.
 
Regards
Cobus
 

1 ACCEPTED SOLUTION

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.

 

relationship.PNG

 

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.Smiley Happy

 

Reference: Create and manage relationships in Power BI Desktop

 

Regards

View solution in original post

6 REPLIES 6
v-ljerr-msft
Employee
Employee

Hi @cobus_19,



And if I add an additional filter to my COUNTX statement the numbers don't quite add up.

What do you mean about "the numbers don't quite add up"? Could you be more precisely with that?

 

And Could you post your table structure with sample data in this case? It's better to share a sample pbix file.Smiley Happy

 

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")

 

Capture.PNG

 

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.

 

relationship.PNG

 

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.Smiley Happy

 

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

Hi @v-ljerr-msft

 

This worked perfect! Thank you very much!

 

Regards

Cobus

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.