Count and sum by creating groups/bins filtered on date

[Anonymous]

Hi ,

Below mentioned is a sample data. I need to create grouping / bin and show count of name and sum in each group based on date filter selected on any 1 particular date.

Date	Name	Sum
01-01-2020	A	29
01-01-2020	A	20
01-01-2020	B	25
01-01-2020	C	9
01-01-2020	D	11
02-01-2020	A	30
02-01-2020	B	27
02-01-2020	C	4
02-01-2020	C	2
02-01-2020	D	2
02-01-2020	D	20
02-01-2020	E	27

This is the expected output for any 1 date that I can select using date filter.

01-01-2020
	Count	Sum
0-20	2	20
21-40	1	25
41-60	1	49
61+	0	0
02-01-2020
	Count	Sum
0-20	1	6
21-40	4	106
41-60	0	0
61+	0	0

Any suggestion how to achieve this?

I tried using summarize function but was not able to do.

Regards,

Amit Darak

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You could try to achieve it through a matrix.

1.Create a Category table by entering data to put the range.

2.Create these measures.

Sum1 =
CALCULATE (
    SUM ( 'Table'[Sum] ),
    ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Name] )
)

NewSum =
VAR _020 =
    CALCULATE ( [Sum1], FILTER ( 'Table', [Sum1] >= 0 && [Sum1] <= 20 ) )
VAR _2140 =
    CALCULATE ( [Sum1], FILTER ( 'Table', [Sum1] >= 21 && [Sum1] <= 40 ) )
VAR _4160 =
    CALCULATE ( [Sum1], FILTER ( 'Table', [Sum1] >= 41 && [Sum1] <= 60 ) )
VAR _60 =
    CALCULATE ( [Sum1], FILTER ( 'Table', [Sum1] >= 61 ) )
RETURN
    SWITCH (
        MAX ( 'Category'[Category] ),
        "0-20", IF ( ISBLANK ( _020 ), 0, _020 ),
        "21-40", IF ( ISBLANK ( _2140 ), 0, _2140 ),
        "41-60", IF ( ISBLANK ( _4160 ), 0, _4160 ),
        "61+", IF ( ISBLANK ( _60 ), 0, _60 )
    )

Count = 
VAR _020 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Name] ),
        FILTER ( 'Table', [Sum1] >= 0 && [Sum1] <= 20 )
    )
VAR _2140 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Name] ),
        FILTER ( 'Table', [Sum1] >= 21 && [Sum1] <= 40 )
    )
VAR _4160 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Name] ),
        FILTER ( 'Table', [Sum1] >= 41 && [Sum1] <= 60 )
    )
VAR _60 =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Name] ), FILTER ( 'Table', [Sum1] >= 60 ) )
RETURN
    SWITCH (
        MAX ( 'Category'[Category] ),
        "0-20", IF ( ISBLANK ( _020 ), 0, _020 ),
        "21-40", IF ( ISBLANK ( _2140 ), 0, _2140 ),
        "41-60", IF ( ISBLANK ( _4160 ), 0, _4160 ),
        "61+", IF ( ISBLANK ( _60 ), 0, _60 )
    )

3.Create a matrix as follows.

4. You can filter by date through the date slicer.

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous - Create a calculated column like this:

Column =
  SWITCH(TRUE(),
    [Sum]<=20,"0-20",
    [Sum]>20 && [Sum]<=40,"21-40",
    [Sum]>40 && [Sum]<=60,"41-60",
    "61+"
  )

Then you can use this column in a visual and use default aggregations.

[Anonymous]

@Greg_Deckler  - It does not give the output as expected. 

[Greg_Deckler]

@Anonymous Can you be more specific?

[amitchandak]

@Anonymous , refer these

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bins
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-in-power-bi/