Dynamic Grouping

[dyee4613]

I'm building a report that classifies people on income.  I've created a measure to sum up their wage and then a long if statement to classify what range the user falls into.  The issue is my report needs to look like this:

	Under 30	Under 60	Over 60
0-10,000	5	1	12
10,001-20,000	1	3	5
20,001-30,000	1		8

The problem is I'm not sure how to dynamically group the numbers so it will modify as the date changes.  Initially, I built a measure that calculates total wages and then created another measure to group them into each class.  However, I can not use the measure for the rows in my Matrix.  I tried to switch it to a calculated column but the calculated column will not change based on the date. 

Anyone have any ideas?  I was thinking a stand alone table with all my groupings and then referencing them with a measure would work but I'm not sure how to do that.

[dax]

Hi dyee4613,

I am not sure your data structure, you could refer to my design:

My sample

name	month	amount	older
a	1	10	3
a	2	20	3
a	3	20	3
a	4	30	3
a	5	50	3
b	1	10	5
b	2	2	5
b	3	30	5
b	4	20	5
b	5	5	5
c	1	10	3
c	2	50	3
c	3	60	3
c	4	9	3
c	5	50	3
d	1	20	5
d	2	30	5
e	1	100	5
e	2	20	5
e	3	50	5

I create a column like below

category =
VAR temp =
    CALCULATE ( SUM ( 'sum'[amount] ), ALLEXCEPT ( 'sum', 'sum'[name] ) )
RETURN
    SWITCH (
        TRUE (),
        temp < 100, "<100",
        temp < 150, "100-150",
        temp < 200, "150-200",
        temp > 200, ">200"
    )

Then create a measure like below

Measure = CALCULATE(distinctCOUNT('sum'[name]), ALLEXCEPT('sum','sum'[older],'sum'[category]))

Best Regards,
Zoe Zhi

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

[Ashish_Mathur]

Hi,

Share data in a form that can be pasted in an Excel workbook.