Averaging Per Category

[jmcph]

Hi, 

I am doing some averaging computations, and my computations seems to be quite off. I just want to ask for correction on why my measure doesnt seem to work. My data are as follows : 

On my Masterlist table:
Borrower                         Attribute                            Date                               Value 

ABC                                  Loan                                   1/1/2019                        1000

ABC                                  Collection                           1/5/2019                        300
ABC                                  Loan                                   2/5/2019                        3000

My measures were as follows : 

AverageLoan =

Var

    SumLoan = Calculate (

Sum( Masterlist[Value] ), Masterlist[Attribute] = "Loan Amount")

Return

    AVERAGEX (

All( 'Calendar'[Date]) , SumLoan )

What i am after is to compute for the Average on a per Borrower basis. My slicer includes both Months and Years.  

When i entered the abovementioned measure it shows me a result of 4,000 (for the year 2019) which i expect to return a value of 2,000. Please enlighten me on what did i do wrong. 

Thank you very much for any help that you may provide! Cheers! 

[CNENFRNL · Accepted solution]

Hi, @jmcph , the cause of problem lies in leveraging a variable within iteration.

In fact, variable in DAX is NOT always variable, refer to:

https://radacad.com/caution-when-using-variables-in-dax-and-power-bi

In DAX, variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression.

To be more precise with your issue, when AVERAGEX iterates All( 'Calendar'[Date]) (being "1/1", "1/5", "2/5"), SumLoan is used 3 times with the same value, which is calculated on iterating "1/1". Furthermore, SumLoan is define in the outmost scope of the measure, only initial filter context instead of "1/1", such as slicer, row/column of viz etc., is used in the calculation.

[vanessafvg]

I think you might be overcomplicating it

AverageLoan = CALCULATE(AVERAGE(MasterList[Value]), MasterList[Attribute] = "Loan")

[jmcph]

Yeah. Thanks for the input. I think i'll do normal average calculation first, then make another measure filtering the dates to make it simpler. I appreciate it thanks!