Forum Discussion
Dynamic Axis on Bar chart
Hello,
I have a table visuals with customer data. I have data like current quarter sales, previous quarter sales, Prior to previous quarter sales. I created all this using measure. Other measures i created are (assuming current quarter is q3-2025) :
1) Current quarter + Previous quarter sales (Q3-25 + Q2-25) for the assigned group. ex :
| Customer | group | Sales | Sales by group |
| A | x | 1 | 3 |
| B | x | 2 | 3 |
| C | y | 5 | 9 |
| D | y | 4 | 9 |
| E | z | 8 | 8 |
2) Similarly quarter sales for group in previous quarter+ quarter before quarter (Q1-25+Q2 - 25).
3) Based on first calculation Current quarter + Previous quarter sales (Q3-25 + Q2-25) upcoming quarter assigment is given. If sales is <25 then base. if sales is between 26 and 50 then medium and if sales is greater than 50 then High.
4) based second calculation group in previous quarter+ quarter before quarter (Q1-25+Q2 - 25) current quarter assigment is given. If sales is <25 then base. if sales is between 26 and 50 then medium and if sales is greater than 50 then High.
5) I created a measure to calculate for which customer the assignemnt is growing or declining. and categorise it in 2. ex
if in current quarter assignemnt is base and in next qaurter is medium then Increasing. if in current quarter assigment is high and in upcoming quarter it is medium then Decreasing. The ascending order is Base, Medium and High. Accordingly customer are categorised in Increasing/ Decreasing.
Now i want to create a bar/Column chart with Increasing/Decreasing as axis and number of customers into the category. As the category are dependent on Sales and sales are dependent on the date slicer in the report (between date) everything here is dyanmic and i am facing some issue while creating the graph. If the dates between changes the sales changes and the category also changes.
Sales for previous quarter and quarter before previous will not be affected by date slicer but current quarter sales will be affecting with the slicer and similarly other measure will change,
Hi swalke ,
Its because your “Increasing/Decreasing” (or other assignment groups) are calculated by a DAX measure that depends on sales and the current quarter, the actual categories can change (appear/disappear) every time your date slicer moves. That makes your chart axis dynamic and hard to keep stable, which is frustrating for both devs and users.
The best practice is to create a separate table in Power BI that lists all possible assignment categories you want to show on your chart, like “Increasing”, “Decreasing”, “Base”, “Medium”, “High”, etc. This is sometimes called a “scaffold” table or static axis.Steps:
-
Create a Categories Table:
Go to “Home” → “Enter Data” and add a single column with every category you need. -
In your bar/column chart, use this new table as your X (or Y) axis, not the assignment calculated by your measure.
-
Write your DAX measure to count customers (or whatever you need) for each category, using your logic for “Increasing”, “Decreasing”, etc.
- Put your AssignmentCategory field from the static table on the axis, and your measure as the value. Now, all categories will always show, even if there are zero customers in a group for the current slicer selection.
-
6 Replies
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi swalke ,
Its because your “Increasing/Decreasing” (or other assignment groups) are calculated by a DAX measure that depends on sales and the current quarter, the actual categories can change (appear/disappear) every time your date slicer moves. That makes your chart axis dynamic and hard to keep stable, which is frustrating for both devs and users.
The best practice is to create a separate table in Power BI that lists all possible assignment categories you want to show on your chart, like “Increasing”, “Decreasing”, “Base”, “Medium”, “High”, etc. This is sometimes called a “scaffold” table or static axis.Steps:
-
Create a Categories Table:
Go to “Home” → “Enter Data” and add a single column with every category you need. -
In your bar/column chart, use this new table as your X (or Y) axis, not the assignment calculated by your measure.
-
Write your DAX measure to count customers (or whatever you need) for each category, using your logic for “Increasing”, “Decreasing”, etc.
- Put your AssignmentCategory field from the static table on the axis, and your measure as the value. Now, all categories will always show, even if there are zero customers in a group for the current slicer selection.
-
Hi swalke ,
Thanks for reaching out to the Microsoft fabric community forum.
Thanks for your prompt response,
I wanted to follow up and confirm whether you’ve had the opportunity to review the information provided by rohit1991 , FBergamaschi .
We truly appreciate your continued engagement and thank you for being an active and valued member of the community.
If you're still experiencing any challenges, please don’t hesitate to reach out we’d be more than happy to assist you further.
We look forward to hearing from you.
Best regards,
LakshmiHi swalke ,
I just wanted to check if your issue has been resolved. If you still have any questions or need help, feel free to reach out I’m happy to assist.
Thank you for being an active part of the community. Looking forward to hearing from you!
Best regards,
LakshmiHi swalke ,
We haven’t heard back from you regarding our last response, so I just wanted to check in to see if you were able to resolve the issue. If you have any further questions or need assistance, please feel free to let us know.
Best Regards,
Lakshmi
