cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Deamo Frequent Visitor
Frequent Visitor

Summarize filtered results

I have a table of customers and the number of times they have called each day, as below:


Date - CustomerPh - TimesCalled

 

I want to get to a level of displaying how many consumers have called x number of times within the date range selected on the report. The grouping will be:

1 call - [Customer Count]
2 calls - [Customer Count]

...

7+ calls - [Customer Count]

 

Ive tried to summarize with filter to get a total count of customers calling within the date range - this is just returning totals. 

In the dax below, Max & MinDateRng are measures that return the start and end of the selected date range

 

CustomerCalls = SUMMARIZE(
     filter(RepeatCallers
       ,RepeatCallers[CallDate]>=[MinDateRng] && RepeatCallers[CallDate]<=[MaxDateRng])
        ,RepeatCallers[CallerNumber]
        ,"CustCount"
        ,sum(RepeatCallers[CountOfCalls]
       )
      )

 

Edit: after playing around some moreit looks like Summarize is evaluated prior to the report filter being applied, and therefore the date isn't picked up in the measure so i get totals instead.. is that right?? if so is there any way around that?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Summarize filtered results

Hi @Deamo,

 

You can refer to below sample to get the total count of specify range.

 

Original Table:

Capture.PNG

 

 

Create a CALENDAR table with original date:

 

Table = CALENDAR(MIN(Sheet1[Date]),MAX(Sheet1[Date]))

 

Add two measures to get the date range.

 

MinDate = FIRSTDATE(ALLSELECTED('Table'[Date]))

MaxDate = LASTDATE(ALLSELECTED('Table'[Date]))

 

Write a measure to get the total count bease on select date range.

 

CountOfSpecifyRange =
var currNumber=LASTNONBLANK(Sheet1[CallNumber],[CallNumber])
return
SUMX(FILTER(ALL(Sheet1),Sheet1[CallNumber]=currNumber&&AND([Date]>=[MinDate],[Date]<=[MaxDate])),[CountofCall])

 

Create visuals:

 

Slicer.

Capture2.PNG

 

Matrix.

Capture4.PNG

In addition, you can direct use the original table to get the result:

Capture5.PNGCapture6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
3 REPLIES 3
Community Support Team
Community Support Team

Re: Summarize filtered results

Hi @Deamo,

 

You can try to use below formula if it suitable for your requirement:

 

CustomerCalls
var temp=  filter(All(RepeatCallers),RepeatCallers[CallDate]>=[MinDateRng] && RepeatCallers[CallDate]<=[MaxDateRng])
return
SUMMARIZE(temp,[CallerNumber],"CustCount",SUMX(FILTER(temp,[CallerNumber]=EARLIER([CallerNumber])),[CountOfCalls]))

 

 

If above is not help, please share some sample data.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Deamo Frequent Visitor
Frequent Visitor

Re: Summarize filtered results

Thanks @v-shex-msft

 

That doesn't work either - still returning all rows from my Repeat Callers table, regardless of the date range selected on the report itself.

 

The screenshots below are from a spreadsheet i've created that shows what i'm trying to do..

Raw Data:

Raw_Data.jpgRawData

 

Calculated counts within selected date range

Calc_Data.jpg

Community Support Team
Community Support Team

Re: Summarize filtered results

Hi @Deamo,

 

You can refer to below sample to get the total count of specify range.

 

Original Table:

Capture.PNG

 

 

Create a CALENDAR table with original date:

 

Table = CALENDAR(MIN(Sheet1[Date]),MAX(Sheet1[Date]))

 

Add two measures to get the date range.

 

MinDate = FIRSTDATE(ALLSELECTED('Table'[Date]))

MaxDate = LASTDATE(ALLSELECTED('Table'[Date]))

 

Write a measure to get the total count bease on select date range.

 

CountOfSpecifyRange =
var currNumber=LASTNONBLANK(Sheet1[CallNumber],[CallNumber])
return
SUMX(FILTER(ALL(Sheet1),Sheet1[CallNumber]=currNumber&&AND([Date]>=[MinDate],[Date]<=[MaxDate])),[CountofCall])

 

Create visuals:

 

Slicer.

Capture2.PNG

 

Matrix.

Capture4.PNG

In addition, you can direct use the original table to get the result:

Capture5.PNGCapture6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |