Bucketing values in a chart

[filarap]

Hi all,

I am trying to create bucket chart, showing number of people falling into a bucket, per week, per month, based on Dynamic target calculation.

I am working with cases closed in same day, so i have this dax:

_SDC = CALCULATE(COUNTA(TCE[Same Day]),TCE[Same Day] IN {"TRUE"})

In order to get percentage, I have created this one:

_SDC% = CALCULATE(COUNTa('TCE'[Same Day]), 'TCE'[Same Day] IN { "TRUE" })/[_Survey Sample]+0

And Dynamic target i use is (These are buckets i need in a chart):

_SDC onTarget = if([_SDC%]>[__SDC% Target],1,if([_SDC%]>[__SDC% Target]/1.2,2,if([_SDC%]>[__SDC% Target]/1.4,3,4)))

Below is a matrix table created:

And based on this table i need a stacked column chart showing number of people in each bucket, per week, per month:

I have found various articles on this forum, but was unable to apply it for myself. Any help is appreciated

Kind regards

Filarap

[v-juanli-msft]

Hi @filarap 

I can'd find any information about "bucket" from your post.

i create a test example, Is the "bucket" already in the table or not?

bucket	date	same day
a	1/1/2019	TRUE
a	1/2/2019	TRUE
a	1/3/2019	TRUE
a	1/4/2019	TRUE
a	1/5/2019	TRUE
a	1/6/2019	TRUE
a	2/1/2019	TRUE
a	2/2/2019	TRUE
a	2/3/2019	TRUE
a	2/4/2019	TRUE
a	2/5/2019	TRUE
b	1/1/2019	TRUE
b	2/1/2019	TRUE
b	2/2/2019	TRUE

Could you show an exmaple data to let me know the data structure?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[filarap]

Hi Maggie.

I did not create buckets yet. What i did create is the dax function that is setting up a different target each month, based on certain criteria within the data. Any % can be set as a target here. I have applied conditional formating, based on this target. You can see the dax function setting bucket treshold above.

Below are table data example of relevant columns

Case Duration	WeekStart	Same Day	People	Survey Sample
0.004664352	26/11/2018	TRUE	7	1
4.855381944	26/11/2018	FALSE	6	1
5.062164352	19/11/2018	FALSE	3	1
0.010983796	26/11/2018	TRUE	10	1
0.816400463	26/11/2018	TRUE	7	1
0.026550926	19/11/2018	TRUE	10	1
3.949016204	19/11/2018	FALSE	10	1
0.051875	12/11/2018	TRUE	5	1
0.037731481	19/11/2018	TRUE	1	1
0.003865741	26/11/2018	TRUE	3	1
5.964594907	19/11/2018	FALSE	9	1
10.888125	26/11/2018	FALSE	1	1
1.852303241	12/11/2018	FALSE	4	1
2.027476852	12/11/2018	FALSE	5	1
13.97271991	19/11/2018	FALSE	9	1
2.138171296	19/11/2018	FALSE	9	1
4.952349537	19/11/2018	FALSE	3	1
4.817337963	19/11/2018	FALSE	5	1
4.053009259	19/11/2018	FALSE	4	1
12.0850463	19/11/2018	FALSE	2	1
2.011712963	19/11/2018	FALSE	6	1
3.06244213	19/11/2018	FALSE	3	1
38.98818287	19/11/2018	FALSE	9	1
4.677303241	26/11/2018	FALSE	8	1
36.0149537	26/11/2018	FALSE	6	1
2.237060185	26/11/2018	FALSE	8	1
0.97787037	26/11/2018	TRUE	2	1
3.849837963	26/11/2018	FALSE	1	1
1.060590278	26/11/2018	FALSE	1	1
2.92556713	26/11/2018	FALSE	8	1
10.57021991	19/11/2018	FALSE	3	1
6.830324074	19/11/2018	FALSE	2	1
4.23212963	19/11/2018	FALSE	4	1
7.894641204	19/11/2018	FALSE	3	1
7.861412037	19/11/2018	FALSE	9	1

Hope this makes it clearer

Filarap

[v-juanli-msft]

Hi @filarap 

As tested, it seems impossible to achieve this requirements.

If you have to achieve this goal, please post another one to let more people see your problem.

Best Regards

Maggie