Forum Discussion

lailailaieric's avatar
lailailaieric
Regular Visitor
9 years ago

Average Line across the Bar Chart

Hiii.    As I am trying to draw an average line for my bar chart, the numbers showed up as the average of the place themselves instead of the average as a whole. Can anyone solve this? Below would ...
Need Help
Show and Tell
Tutorial Requests
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi lailailaieric,

Have you resolved your problem? If it did, I am glad to hear that, and please mark the helpful reply as answer,so that more people can find workwroud form here. Thanks for understanding.

Best Regards,
Angelia

S184019's avatar
S184019
Icon for Advocate III rankAdvocate III
8 years ago

Hi v-huizhn-msft

 

Using DAX, I found that I am needing help on the same matter. Here is one of the lines on the char

 

SchedLagMedian = MEDIAN('Locations'[Important_Column])

Here is another line. 

 

 

Month/Year = FORMAT(DATE(LEFT('Locations'[Months], 4), RIGHT('Locations'[Months], 2), 1), "MMM-YY")

lastly, here is the horizontle line like what I am trying to replicate dynamically

 

 

LagTarget = 14

Essentially I need a horizontle line like that above but referenced from another chart.  That code is here: 

 

 

Summarized Lag Table = 
UNION((ADDCOLUMNS(SUMMARIZE('Locations',Locations[Areas],"Value", 14,"Target", BLANK()
    ),"Type", "Goal", "Sort", 1 )),
CALCULATETABLE(ADDCOLUMNS(SUMMARIZE('Locations',Locations[Areas]), 
        "Value", CALCULATE(MEDIAN('Something'[Important_Column])),
        "Target", BLANK(),
        "Type", "Performance",
        "Sort", 2 ),
    'Something'[Time] = 1 ),
ADDCOLUMNS(SUMMARIZE(Locations, Locations[Areas]),
        "Value", SUMMARIZE('Lag Medians', "Average", AVERAGE('Lag Medians'[Value])),
        "Target", BLANK(),
        "Type", "All Locations",
        "Sort", 3))

 

 

This produces a line graph that has a flat horizontle line and a performance line.  Another line needs to exist which is the calculated median of performance.  Perhaps something like this... 

 

Median = MEDIAN(SUMMARIZE('Locations'[Important_Column])) 

 

 

But when I put that in, I simply get the same as if I added the median at each month rather than the average median.  Please help? Thanks in advance!

 

Mark

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    8 years ago

    Hi S184019,

     

    Instead of the MEDIAN in the summarize, try to use the MEDIANX, this is an aggregator formula that does the median but calculating the values row by row and then returning the final result.

     

    Regards,

    MFelix

     

    • S184019's avatar
      S184019
      Icon for Advocate III rankAdvocate III
      8 years ago

      Hey MFelix,

       

      Thank you for your super quick reply!  The difficulty might be a bit deeper than that.  I am not able to medianx of summarized('Locations'[Important_Column])) but that truly is a GREAT suggestion and point. 

       

      Sincerely,

       

      Mark

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        8 years ago

        Hi S184019,

         

        Without some data sample is difficult to give a better respons although you have the measures you use the data is fundamental to have the right response, if you can share a sample and expected result would be easier to give you a more accurate answer.

         

         

        Regards,

        MFelix