Recreate Average Line in the Analytics Tab

[Anonymous]

Hello Everyone,

I am trying to duplicate a time series analysis called Wheeler Variation to investigate my Bookings data.

Columns:

• MonthYear, Year, Month self explanatory
• Bookings by Order Date = CALCULATE(SUM('Bookings2018-2021'[AmountinUSD]), USERELATIONSHIP(vw_dim_Date[Date], 'Bookings2018-2021'[OrderDate]))

• Bookings LM = CALCULATE([Bookings by Order Date], DATEADD(vw_dim_Date[Date], -1, MONTH))
• Bookings Moving Range =

                 VAR vBook = [Bookings by Order Date]

                 VAR vBookM1 = [Booking LM]

                 VAR vBookDiff= vBook-vBookLM

                 RETURN

                 ABS(vBookDiff)

•  Bookings MR Avg Summarize = 

                   VAR vBMRAvg = AVERAGEX(VALUES(vw_dim_Date[MonthYear]), [Bookings Moving Range])

                   VAR vAvgTotal = SUMMARIZE(vw_dim_Date, vw_dim_Date[MonthYear], "Monthly Avg", vBMRAvg)

                   RETURN

                   IF(HASONEVALUE(vw_dim_Date[MonthYear]), vBMRAvg, AVERAGEX(vAvgTotal, [Monthly Avg]))

Here is where I am stuck:

I want to add another column to my table that displays the "Total" for the [Bookings MR Avg Summarize] in all the rows.  So there would be another column in the table where every row has the value 1,737,008.18. 

• (This would allow me to duplicate the Average dashed orange line from the Analytics tab)

I am able to do this using th Analytics Average Line as seen in the Line Chart Visual as the orange dashed line.  However, I don't actually need the average (1,737,008.18) displayed on the chart.  For the Wheeler Variation I need to take 1,737,008.18 * 3.27 so that I can display a line on the chart with a value of approximately 5.4million (see my not straight red line).

Then I will be able to Conditionally Format the background of the chart so that it is RED if the Bookings Moving Range is above the line and it is GREEN if below. 

I thought I had it with the [Bookings MR Avg Summarize] Measure, but as you can see when I put it into the chart, it ends up mimicking the [Bookings Moving Range] Measure.  Somehow I need a calculation where it ignores the MonthYear in the chart and gives me the straight line.

I have tried so many ALLs, and ALLSELECTEDs that I am going insane,

Thank you very much if anyone out there can help me out,

Kyle

[Anonymous]

Hello Everyone,

I will try to get permission to sterilize the data and post OR I will create a randon dataset at some point.

Darksky

[V-lianl-msft]

Hi @Anonymous ,

It is very difficult to analyze without looking at the data and just by imagining.

Could you share the sample pbix via cloud service like onedrive for business?

Please remove any sensitive data before uploading.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

@Anonymous 

Please read this thread to help us help you:

How to get your question answered quickly