Dynamic grouping of of X-Axis values using Slicer

[abdulmannan1991]

Hello. For one of my reports, I create Spend Grouping based on spend values to populate the X-Axis of visual. Please see attached image of visual and backend data.

For Example, the data has got spend values in a column. I classify all spend values into Spend Groups manually (using IF statement in Excel or Power BI). Like, all the values between 0 to 1,000 have been tagged as ''(01) $0 - $1k" as shown in 1st bar of visual, and the bar is representing the sum of spend in that spend group, and so on.

What I want is to make X-Axis selection dynamic within Power BI. That is, the user (report viewer) should be able to define and adjust the spend groups (X-Axis).

For Example, if a user wants to expand/change the X-Axis range and see sum of spend in different groups (like, '$0 - $200' group, and then '$201 - $500, and so on), he should be able to define/ make adjustment to the X-Axis within Power BI (may be using a Slicer or by inserting Axis range values, or something like that).

Please advice how to do that using a slicer, parameter, measure, DAX, etc.

Thank you.

[Mohammedhassan]

Hi Abdulrahman, 

Do you find any solution to this problem as I face the same issue? 

[abdulmannan1991]

Hi @Mohammedhassan  Not yet.... Still looking for the solution.

[amitchandak]

@abdulmannan1991 ,

Check if this can help : https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[abdulmannan1991]

Thanks @amitchandak but it didn't help.

The requirement is to allow the users to define the X-Axis (like, by inserting spend grouping values or adjusting them using a slicer, etc).

[Greg_Deckler]

Right, so short of allowing them edit capabilities, here are the options available:

• As done in the dynamic everything link I sent where you essentially have a structure like

X-Axis,Slicer

One,Option1

Two,Option1

Three,Option1

Red,Option2

Green,Option2

Blue,Option2

So for Option1 you have One,Two,Three on your axis and for Option2 you have Red,Green,Blue on your axis.

• Or you can go the stacked visuals and bookmarks route where you create stacked visuals for each x-axis and bookmarks that show the appropriate visual and hide the others. 

If those options are not viable there likely isn't an answer to do what you want. But, to be sure, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[abdulmannan1991]

@Greg_Deckler 

Thank you. I still can't seem to implement your solution to my case. But it is also a fact that I didn't understand it completly as I'm just a beginner in Power BI DAX, Modeling, etc.

Can I request you to do this in Power BI for me and share the pbix file? I have uploaded a test dataset (which contains spend values and manually calculated spend groups) and pbix (which represents my desired chart).

Link for test files: https://we.tl/t-1JNqAXBdVY.

If you see the 'Spend Group' column, that is prepared manually using IF formula. What i want is to:

1). have this groupipng defined within Power BI (and not manually in backend Excel).... may be using a DAX measure.

2). have dynamic selection of X-Axis grouping. That is, instead of manually pre-defined group ranges ($0-$1k, $1k-$2k, and so on), the user (report veiwer) should be able to define the preferred group/range, for example, if the user wants to see the sum of spend in a different group (say, $0-$250, $250-$500, etc...), he should be able to define this axis in live report and then the bar chart would show sum of values accordingly in those groups. 

Many thanks!

[v-lid-msft]

Hi @abdulmannan1991 ,

Sorry for that but we cannot access the test file you shared, Could you please upload your test file to OneDrive For Business and share the link here? 

Please don't contain any Confidential Information or Real data in your reply.

Best regards,

[abdulmannan1991]

@v-lid-msft Sorry, don't have OneDrive. Please can you download the file from below-given WeTransfer link. I just checked and it's working:

https://we.tl/t-1JNqAXBdVY

Thank you.

[Greg_Deckler]

Maybe:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titl...

[abdulmannan1991]

Thanks @Greg_Deckler but it didn't help.