AvgX not working on "Total" line

[Dellis81]

Good Morning

I'm really struggling with somethign I should be able to figure out.   Kindly asking assistance on getting the proper subtotal to display AvgX

File link:  https://drive.google.com/file/d/10_xyTaNR6GtDWNoik_UooN9jHX2aSCnX/view?usp=sharing

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...

thanks!
thank you!

[Dellis81]

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

1. I was hoping to use the Percent changes inside table calculation KPIs- as trend indicators.  So ideally I was hoping to keep as a single measure – that I could use within the “Trend” KPI
2. The example provided – is a summary by year for all combines
3. The next concept I played with (but did not include) – would be to reverse the year and center fields in matrix – and then show life time costs (versus annual avg).   And then some type of comparison against the fleet average.
1.   But for now, I am just trying to keep it simple  and learn the proper process that I can use for up/down conditional color indicator signals
4. Do you have other thoughts that would get me to the same point?   I don’t like dollars, as I have other resources (like combines) that have a totally idffernt cost structure, and I thought percentages would help in trend indicators.

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!

[v-jingzhang]

Hi @Dellis81 

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 

Best Regards,
Community Support Team _ Jing

[Dellis81]

Hello 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!

[v-jingzhang]

Hi @Dellis81 

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. 🤔

Best regards,

Jing

[Dellis81]

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.

https://drive.google.com/drive/folders/1-dKYRdtfqyVYNsHLCZrLO_v08gaUFruI?usp=sharing



.

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!!!!!