cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Flawn
Frequent Visitor

Count Bins that adjust w/ Date Slicer for a Stacked Column Chart

Hello Everyone,

I've inherited a very messy dataset that i'm working with in PBI. I'd consider myself still fairly amatuerish when it comes to the program, and I've unable to fulfill a certain design request that was made.

I was provided with a sample image to get an idea of the desired outcome. They also want to remove the limitation that the data display is 'set' to fortnightly.

 

Flawn_0-1634187155096.png


I was able to quite easily arrange the data to categorize clients into the four groups, and the date slicer was also easy: but i've run into some issues with breaking up the # of sessions for each client into bins. While I can create the bins easily enough using the grouping system, the desired outcome would be for the number of entries in each bin to adjust based on the date slicer.  So, as an example, a DFV client that was in the 4-10 sessions bin if the slicer is set to a month, could drop into the 2-3 sessions bin if the slicer is set to the month. At the moment all sessions the client has ever had are counted, regardless of how the slicer has been set. This of course means that everyone is being put into innappropriately high buckets.

The Second issue I am having is a matter of data Display, a shot of the draft chart is provided below:

Flawn_1-1634187986813.png

Flawn_2-1634188018185.png


Simply put, I would like to 'flip' the axis and values. So that the x-axis is dedicated to each category, while the stacks track each bin. The way the data has been prepared, the chart in question doesn't seem willing to make this happen - some guidance as to the appropriate arrangement within the axis/legend/values to make this work would be appreciated. 

While I can't share the file in question - the stuff is very private - I've recreated the format of the relevent data in the table below:

mcs_clientmcs_sessionmcs_sessiondatedisability CALD ATSI 
10D9667625/07/2018 1:30111
1E081275915/10/2018 2:00111
1F16CFA5222/10/2018 1:45111
2982B788F8/01/2019 5:001  
25C6F3A9A4/02/2019 3:301  
30FBED8CB10/07/2019 23:30 1 
4EF2969486/07/2019 0:00 11
44428409911/07/2019 2:30 11
4A8DA3C3911/07/2019 6:00 11
42DB7363B15/07/2019 23:30 11

 
At the moment I'm the way I am generating the number a count of the sessions each client has is through a pretty simple calculated column. I recognize the solution here is probably in a measure: but i am not as confident with those:

 

 

 

# of Sessions in Client = 
CALCULATE(CALCULATE(COUNT(ClientInSessionByCase[mcs_client])), ALLEXCEPT(ClientInSessionByCase, ClientInSessionByCase[mcs_client]))

 

 


Thank you all in advance for any assistance you provide - and your patience with this clumsy, amatuer, PBI user.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @Flawn,

Perhaps you can try or unpivot these value fields to convert them to attribute and value. Then you can use attribute on legend fields and use measure on value field with switch function and if statement to check both axis and legend values at the same time and redirect them to specific calculations.

Unpivot columns (Power Query) (microsoft.com)
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @Flawn,

According to your description, It seems like you want to show your visual graph with dynamic ranges based on aggregate values.
I'd like to suggest you create a table with all ranges, then you can write a measure expression with switch function to calculate based on the current category.

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup...

Static segmentation – DAX Patterns

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@Flawn , based on what I got so far, You need to bucket the measure. for that you need an independent table.

 

refer if these blog can help

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks for the response,

While it's an okay start, I am afraid the solutions you provide do not work well for my particular case.

Following the steps in your video we produced this table:

Flawn_0-1634622872298.png

A good start, but unfortunately this leaves us stuck with the same problem we started with:

Flawn_1-1634622967358.png

 

The # of Sessions is still used for the X Axis, while the categories - ATSI, CALD, etc - are used for the stacks. We need to swap this around.

We tried to fix this ourselves, but the way in which you created your buckets does not translate well to the way our database is arranged. As you can see in the sample we provided above, each of the categories is its own column, and one client can be in multiple columns. This meant that the switch function wouldn't work to generate our bins, nor was the min/max system that you used useful for filtering them.

Thank you in advance for further guidance.

v-shex-msft
Community Support
Community Support

Hi @Flawn,

Perhaps you can try or unpivot these value fields to convert them to attribute and value. Then you can use attribute on legend fields and use measure on value field with switch function and if statement to check both axis and legend values at the same time and redirect them to specific calculations.

Unpivot columns (Power Query) (microsoft.com)
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!