Use existing SUM measure to develop a new AVERAGE measure - is it possible

[Rajkota]

Hi - I have a DAX measure calultaed that calculates SUM of a particular quantity and is sensitive to date based filters - Year, Month etc.,  I'm trying to use that to calulcate a AVERAGE measure on the same FACT measure, and use that as a function to limit the range on the Y axis in a graph.  So for example, I have values of 10, 20, 50, 30, 40 over different weekdays, I want to derive a measure of an average value of 30 as the average.  The 10, 20, 50...are all coming from a exisint measure.  I am able to use the AVERAGE function, but any filter does not apply - it calculates the overall AVERAGE.  I tried using the CALCULATE(AVERAGE(MEASURE),CALENDAR....) and it doesn't work.   would be thanful for any suggestions.  I am trying to avoind going back to the perspective and creatig a new measure there.  I'm trying to create this measure within my PBIX file.

Thank You.

[johnt75]

You can try

Avg Measure = AVERAGEX(
   ADDCOLUMNS( SUMMARIZE( 'Date', 'Date'[Date]), "@val", [Existing Measure]),
   [@val]
)

That will create a daily average during the selected time period.

[Rajkota]

thanks Johnt75 - I tried and it only gives me the sum - here's the formula.   Perhaps I'm doing something wrong here

Avg Measure = AVERAGEX(

ADDCOLUMNS( SUMMARIZE( 'Calendar', 'Calendar'[Year],'Calendar'[Period Num]), "@val", 'Work Ord Production Cost'[WO Ord Amt Actual]),

[@val]

)

[johnt75 · Accepted solution]

Is 'Work Ord Production Cost'[WO Ord Amt Actual] a column or a measure? It should be the measure you mentioned in your original post

[Rajkota]

ok thanks.  Here is the updated formual with the Measure used in the formula, as opposed to the column - still gives me only the SUM.  apreciate your help johnt75

Avg Measure = AVERAGEX(

ADDCOLUMNS( SUMMARIZE( 'Calendar', 'Calendar'[Year],'Calendar'[Period Num]), "@val", [WO Ord Amt Actual]),

[@val]

)

[johnt75]

That looks correct to me. What does Calendar[Period num] represent? Also, what are you using on the x-axis of your chart?

[Rajkota]

thanks again johnt75 - your question made me take a closer look, and so I added another calendar filter to the formula - here it is now-  I added three levels of calendar filtersm as I could use any of them slicers when viewing the graph.  This works now.  Many thanks for your help.  Appreciaye it greatly.

Avg Measure = AVERAGEX(

ADDCOLUMNS( SUMMARIZE( 'Calendar', 'Calendar'[Year],'Calendar'[Calendar Month],'Calendar'[Calendar Day Num]), "@val", [WO Ord Amt Actual]),

[@val]

)