Forum Discussion

rssilvaba's avatar
rssilvaba
Icon for Resolver II rankResolver II
6 years ago
Solved

Need constant line based on a target value

Hi All,

I have been trying to put in the graph a constant line based on a calculated target. All works well but the problem is that the columns are based on markets and what I need is the total value.

So for example in the picture below:

The line is drawn around 0.85 when what I am trying to achieve is to drawn that line around 1.10 which is actually the sum of the two orange bars.

I looked around for such a thing I guess I need to aggregate this values into one otherwise it will get the highest.
Any ideas how I can achieve that?

Thank you
I can post a sample if needed.

Need Help
  • rssilvaba's avatar
    rssilvaba
    6 years ago

    Hello again,

    I am just gonna put how I ended up using it:

     

     

    # Requests Urgent Total = COUNTROWS(SUMMARIZE(FILTER(ALLSELECTED(SampleData),[# Requests Urgent]),SampleData[Id]))
# Requests Total = CALCULATE([# Requests],ALLSELECTED(SampleData))
KPI DoUR Total = CALCULATE([KPI DoUR Target],ALLSELECTED(SampleData))

    With this for any Axis will always show the total not the individual aggregations, which was what I wanted also in the sample you can see that I can have the constant line as a measure as well in the Line and bar chart.

    Here is the sample:
    sample 

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    rssilvaba , Try Clustered bar visual and have a line there. A measure like. Group is your legend column and Orange is your legend value

     

    calculate([measure],filter(all(Table[Group]),Table[Group] ="Orange")

    calculate([measure],filter((Table[Group]),Table[Group] ="Orange")

    calculate([measure],filter(allselected(Table[Group]),Table[Group] ="Orange")

     

    or check https://docs.microsoft.com/en-us/power-bi/desktop-analytics-pane

     

    • rssilvaba's avatar
      rssilvaba
      Icon for Resolver II rankResolver II
      6 years ago

      Hi All, used COUNTROWS(SUMMARIZE(SampleData,SampleData[Id])) and all seems to be correct.

      So if anyone can check the sample and let me know how I can apply your awnser to it I would be glad.
      https://drive.google.com/file/d/1Khdm4SYBNjaBNPQoZ1OamHFGqtm2UXZ1/view?usp=sharing 

      Not related to this question but If you may know this awnser as well. I need to show this as percentages, as the picture below. But when I switch it also changes the target and the data seems all wrong.

      Sorry if this looks simple, I have very little experience with PBI overall but improving.

      Thanks all,

      • rssilvaba's avatar
        rssilvaba
        Icon for Resolver II rankResolver II
        6 years ago

        Hello again,

        I am just gonna put how I ended up using it:

         

         

        # Requests Urgent Total = COUNTROWS(SUMMARIZE(FILTER(ALLSELECTED(SampleData),[# Requests Urgent]),SampleData[Id]))
# Requests Total = CALCULATE([# Requests],ALLSELECTED(SampleData))
KPI DoUR Total = CALCULATE([KPI DoUR Target],ALLSELECTED(SampleData))

        With this for any Axis will always show the total not the individual aggregations, which was what I wanted also in the sample you can see that I can have the constant line as a measure as well in the Line and bar chart.

        Here is the sample:
        sample