topic How to group data and connect them to date slicer from the initial data in DAX Commands and Tips

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

Ninna — Tue, 07 Feb 2023 14:10:57 GMT

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.

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

FreemanZ — Tue, 07 Feb 2023 15:06:28 GMT

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:

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

Ninna — Wed, 08 Feb 2023 11:13:24 GMT

That's it. Thank you 🙂