Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

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

     

    Original Table:

     

     

    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.

     

    Matrix.

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

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thanks Anonymous

 

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:

RawData

 

Calculated counts within selected date range

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Anonymous,

 

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

 

Original Table:

 

 

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.

 

Matrix.

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

 

Regards,

Xiaoxin Sheng