Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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.
thanks Johnt75 - I tried and it only gives me the sum - here's the formula. Perhaps I'm doing something wrong here
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
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
That looks correct to me. What does Calendar[Period num] represent? Also, what are you using on the x-axis of your chart?
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.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |