Forum Discussion
Add constant lines to small multiples
I want to make it possible to represent the purple line in the image within the small multiples as well.
I want to create a visual in Power BI as described below:
1. Use small multiples to create charts divided by "Work Process (STATUS)".
2. Create vertical bar charts with the X-axis set to "Work Days" and the Y-axis set to "Number of Completed Items".
3. Add a purple constant line (as shown in the image) for each "STATUS". The constant line should be based on the reference work time from a master table (reference work time per STATUS).
For example:
- When STATUS is 1, the constant line is at 65 days.
- When STATUS is 2, the constant line is at 15 days.
Using small multiples is mandatory.
I have thought of a way to create and add a measure to the constant line on the X-axis and add it to the Y-axis of the graph, but I don't know how to do it. Could you please teach me the method? If there are other ways, I would appreciate it if you could share them as well.
Hi HANA7B ,
You cannot add dynamic constant lines directly with smal multiples you can however make error bar that can match this.
Try the following steps:
- Select a column and line chart
- Create the following measure:
Constant = SWITCH(TRUE(), SELECTEDVALUE( Products[Class]) = "H" && SELECTEDVALUE('Calendar'[day]) = 20, 1, SELECTEDVALUE( Products[Class]) = "L" && SELECTEDVALUE('Calendar'[day]) = 15, 1, SELECTEDVALUE( Products[Class]) = "M" && SELECTEDVALUE('Calendar'[day]) = 10, 1) Upper = CALCULATE([Total Quantity], REMOVEFILTERS('Calendar'[day])) Lower = 0In my case the Product[Class] is the column I'm using on the small multiples and the Total Quantity is the column I'm using in my visual
- Setup of visual:
- X-Axis: Day
- Column Y-Axis: Total Quantity
- Line Y-Axis: Constant
- Small Multiples Class
- Error Bars:
- By Field
- Upper Bound: Upper measure
- Lower Bound Lower measure
- Relationship to measure: Absolute
- Bar: on
- Color selet to you needs
- Turn of lines so that the marker does no show
- Turn off values in secondary axis
Final Result:
4 Replies
Hi HANA7B ,
You cannot add dynamic constant lines directly with smal multiples you can however make error bar that can match this.
Try the following steps:
- Select a column and line chart
- Create the following measure:
Constant = SWITCH(TRUE(), SELECTEDVALUE( Products[Class]) = "H" && SELECTEDVALUE('Calendar'[day]) = 20, 1, SELECTEDVALUE( Products[Class]) = "L" && SELECTEDVALUE('Calendar'[day]) = 15, 1, SELECTEDVALUE( Products[Class]) = "M" && SELECTEDVALUE('Calendar'[day]) = 10, 1) Upper = CALCULATE([Total Quantity], REMOVEFILTERS('Calendar'[day])) Lower = 0In my case the Product[Class] is the column I'm using on the small multiples and the Total Quantity is the column I'm using in my visual
- Setup of visual:
- X-Axis: Day
- Column Y-Axis: Total Quantity
- Line Y-Axis: Constant
- Small Multiples Class
- Error Bars:
- By Field
- Upper Bound: Upper measure
- Lower Bound Lower measure
- Relationship to measure: Absolute
- Bar: on
- Color selet to you needs
- Turn of lines so that the marker does no show
- Turn off values in secondary axis
Final Result:
Hi HANA7B ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @HANA7B ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
