Add constant lines to small multiples

[HANA7B]

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.

 

 

 

 

[v-dineshya]

Hi @HANA7B ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @MFelix , Thank you for your prompt response.

Hi @HANA7B ,  Could you please try the proposed solution shared by @MFelix ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[v-dineshya]

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

[v-dineshya]

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

[MFelix · Accepted solution]

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 = 0

In 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: