I'm really struggling with somethign I should be able to figure out. Kindly asking assistance on getting the proper subtotal to display AvgX
The value in red should be Average(-0.26, 0.13, 0.10) = -0.007
The value in blue should be Average (-0.13, 0.12, 0.05) = 0.009
The value hilighted in yellow should be "blanked" out.
YOY % Change = Var ThisYrValue = [$/CDU] VAR LastYrValue = CALCULATE ( [$/CDU], PREVIOUSYEAR('Calendar'[Date])) Var IsValid = if(and(ThisYrValue<>BLANK(),LastYrValue<>BLANK()),TRUE(),FALSE()) VAR AvgYr = AVERAGEX ( FILTER ( VALUES ( 'CALENDAR'[Year] ), 'CALENDAR'[Year] < YEAR ( TODAY () ) ), Var V1 =DIVIDE ( [$/CDU], CALCULATE ( [$/CDU],PREVIOUSYEAR('Calendar'[Date]) )) - 1 Var Single = If(isvalid,v1,blank()) Return Single ) Return AvgYr
Percent Chge From Trend = if([mDollars]=blank(),blank(),DIVIDE([$/CDU],calculate([MovingAvgTrend]))-1) /* AVERAGEX ( FILTER ( values ( 'CALENDAR'[Year]) , 'CALENDAR'[Year] < YEAR ( TODAY () ) ), if([mDollars]=blank(),blank(),DIVIDE([$/CDU],calculate([MovingAvgTrend]))-1) )*/
Note - I realize I have the AvgX commented out - pulling it in results in wacky values, so for demo, I included the measure outside the AvgX iterator
I really struggle with these type measures - especially if start including variables or if statements inside the iterator. Is anyone aware of a resource relating to iterators I can learn from. I've read a million blogs, but still don't get it 🙂
My next step is to start building KPI's. Is it advisable to put these type measures inside a calculation table KPI trend. I may need help with that also...
Thank you for the update.
For now – split the measures out to get it to work. I can use those as part of our development process.
Longer term – couple goals
Again – thank you so much. I am travelling next few days and wont bet back to this project for a week or so.
Have a great day!
Sorry there are many measures involved in the file, so it's a little difficult to understand what the expected result should be. And when you want the proper subtotal average values, what is the mathematical formula?
To my knowledge, I got the following average results. I think I didn't work out the correct formula probably, can you provide the mathematical formula for it?
(-0.26 + 0.13 + 0.10)/3 = -0.01 or (-0.26 + 0.13 + 0.10)/4 = -0.0075
(-0.13 + 0.12 + 0.05)/3 = 0.0133 or (-0.13 + 0.12 + 0.05)/4 = 0.01
Community Support Team _ Jing
Have you had a chance to review the latest file? You had mentioned being close and wanted confirmation, and then I went in and "cleaned up" and attempted to simplify the measures. I will be "on the road" again for close to a week, so was hoping to have closure - before I start change mental gears.
Thank you for your help and assistance. This forum is awesome for somebody like me to learn and grow - and respect and appreciate your time. Thanks!
Sorry for the delay. I haven't worked out all. This is my current progress. YOY % Change 3 seems return the correct result. (YOY % Change 2 is used for calculation.)
I want to break Percent Change From Trend to get this year's value and last year's value first, but the last year value is always incorrect. This is frustrating.
I'm thinking if you only want to have results for Combine group and years and don't need to expand data to centers, just like in the Excel, I'd like to create all measures from scratch maybe. As of now, I think for a single year, using Average and Sum will get the same result. I'd like to avoid using so many Averagex if possible. 🤔
Thank your for responding a few days ago. I've had several long days "on the road", so just now getting caught up.
I did remove a bunch of commented out formulas - so maybe that will help you for clarity. I apologize!
In excel, I'm using the simple average function, with the blank value not being counted. So, in your example the denominator would be 3.
As I have been thinking thru - not sure if my % change from moving average trend is correct. Excel column H is the expected value, but as you see, that same column in PBI is coming in @100%.
I added a second trend column - which is the moving avg trend excluding current year. Unfortunately, PBI is pulling in the actual $/CDU from prior year, versus prior year tend.
Included in folder share are an excel file with expected results (and formulas) and my most recent PBI test file.
I did add a second tab - looking at lifetime costs per combine, please ignore for now. I want to get the annual comparison in place, before I start something new. Thank you!!!!!
Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!
Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!
Click here to read more about the May 2022 updates!