Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Bucketing values in a chart

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
Need Help

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 DurationWeekStartSame DayPeopleSurvey Sample
      0.00466435226/11/2018TRUE71
      4.85538194426/11/2018FALSE61
      5.06216435219/11/2018FALSE31
      0.01098379626/11/2018TRUE101
      0.81640046326/11/2018TRUE71
      0.02655092619/11/2018TRUE101
      3.94901620419/11/2018FALSE101
      0.05187512/11/2018TRUE51
      0.03773148119/11/2018TRUE11
      0.00386574126/11/2018TRUE31
      5.96459490719/11/2018FALSE91
      10.88812526/11/2018FALSE11
      1.85230324112/11/2018FALSE41
      2.02747685212/11/2018FALSE51
      13.9727199119/11/2018FALSE91
      2.13817129619/11/2018FALSE91
      4.95234953719/11/2018FALSE31
      4.81733796319/11/2018FALSE51
      4.05300925919/11/2018FALSE41
      12.085046319/11/2018FALSE21
      2.01171296319/11/2018FALSE61
      3.0624421319/11/2018FALSE31
      38.9881828719/11/2018FALSE91
      4.67730324126/11/2018FALSE81
      36.014953726/11/2018FALSE61
      2.23706018526/11/2018FALSE81
      0.9778703726/11/2018TRUE21
      3.84983796326/11/2018FALSE11
      1.06059027826/11/2018FALSE11
      2.9255671326/11/2018FALSE81
      10.5702199119/11/2018FALSE31
      6.83032407419/11/2018FALSE21
      4.2321296319/11/2018FALSE41
      7.89464120419/11/2018FALSE31
      7.86141203719/11/2018FALSE91

       

      Hope this makes it clearer

      Filarap

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous 

        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