Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Create average line from analytics using a measure

Hi all,

 

I have a visual that tracks how long it takes a technical service center to resolve various faults using a line and stacked column chart.

The solid line is the average time it take to solve a specific fault. I would like the dashed line to be the average resolution time across all cases but unfortunetly, the line and stacked column chart doesn't support analytics. To resolve this, I made a column that just measures the average of all cases in the query:

 

Dwell = AVERAGE('Case File Data'[Dwell (days)])

 

and set the line value to average but this means that the average line will not dynamically adjust as I input values in slicers. It just stays at 6.1 days.

 

Is there a way to write a measure that will create a flat average line similar to the one from the analytics tab from other visuals? I'm new to BI and haven't been able to find an answer searching.

 

I've tried to write a measure to produce a flat line using 2 measures:

 

Total dwell = SUM('Case File Data'[Dwell (days)])

 

 

Average Dwell = AVERAGEX(ALL('Case File Data'),[Total dwell])

 

I feel like that should produce a flat line but it just matches the other one like this: 

 

Thanks,

 

Robbie

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Have tried a line with allselected ?

    Try Like

    Dwell = calculate( AVERAGE('Case File Data'[Dwell (days)]) , allselected('Case File Data'))
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak 

       

      I had previously tried that just using ALL rather than ALLSELECTED, but I gave your suggestion a try just in case. It seems to also produce a line that matches the line measuring the average per fault rather than a flat reference line.