How to group data and connect them to date slicer from the initial data

[Ninna]

Hi everyone,

I need some help.

I have a sales table by date, customer, ect. I need to create group (range) based on the sum of sales for customers and campaigns.

The problem is that I dont't know how to connect the table by ranges to timline based od date in my data.

Here is the example of what I need. I have the table on the left and I need to get the report looking like the table on the right(that needs to be sensitive on timeline).

Tnx. 

[Ninna]

That's it. Thank you 🙂

[FreemanZ · Accepted solution]

hi @Ninna 

you would need a range table like:

Range	Min	Max
>1500	1501	9999
1000-1500	1001	1500
500-1000	501	1000
0-500	0	500

then plot the Range[Range] column with two measures like:

No of Customers = 
VAR _table =
ADDCOLUMNS(
    SUMMARIZE(
        Sales,
        Sales[Customer],
        Sales[Campaign]
    ),
    "Sales",
    CALCULATE(SUM(Sales[Sales]))
)
VAR _list =
CALCULATETABLE(
    SUMMARIZE(Sales, Sales[Customer], Sales[Campaign]),
    FILTER(_table, [Sales]>=MIN(Range[Min])&&[Sales]<=MAX(Range[MAX]))
)
RETURN 
COUNTROWS(_list)

Total Sales = 
VAR _table =
ADDCOLUMNS(
    SUMMARIZE(
        Sales,
        Sales[Customer],
        Sales[Campaign]
    ),
    "Sales",
    CALCULATE(SUM(Sales[Sales]))
)
RETURN
CALCULATE(
    SUM(Sales[Sales]),
    FILTER(_table, [Sales]>=MIN(Range[Min])&&[Sales]<=MAX(Range[MAX]))
)

it worked like: