Forum Discussion

collmomo's avatar
collmomo
Advocate I
9 years ago
Solved

Measure to Calculated column

I have this measure which was created like that DIO Year-End := ([Net Inventory Month End]*91/[Total COS (Adj.) 3 Mths]) Net Inventory Month End := CALCULATE(SUM(Actual[Amount]),Actual[ID]=1)...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi collmomo,

    It is working but I needed to make an average out of this thus I created a calculated column.

    In this scenario, you should be able to use AVERAGEX Function (DAX) to create a measue to calcuate the average, without creating the calculate column. The formula below is for your reference.:smileyhappy:

    Avg DIO Year-End = AVERAGEX ( Actual, [DIO Year-End] )

    Regards

collmomo's avatar
collmomo
Advocate I
9 years ago

Baskar Thanks for the reply, I can't share the data I'm using though, I already tried what you said to debug, the problem arises ONLY when I do the division, I really don't know why.  

 

v-ljerr-msft AVERAGEX(Actual,[DIO Year-End]) returns NaN

 

I had to change the table to AVERAGEX(DateTable,[DIO Year-End]) to make it work,

 

However I still dont understand why is that working

 

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi collmomo,

However I still dont understand why is that working

While AVERAGE is an aggregator, AVERAGEX is an iterator. They both can end up giving you the same result, but they do it in a very different way.

 

In short, the aggregator(AVERAGE, SUM) operates over a single column of data to give you the result (the aggregation of the single column).  The iterator(AVERAGEX, SUMX) on the other hand is capable of working across multiple columns in a table.  It will iterate through a table, one row at a time, and complete a calculation (like Quantity x Price Per Unit) and then add up the total of all of the row level calculations to get the grand total.

 

To better understand the different behaviors between the aggregator and the iterator, you can refer to the following articles.:smileyhappy:

http://exceleratorbi.com.au/sum-vs-sumx-in-dax/

https://www.powerpivotpro.com/2014/10/sum-sumx-or-calculatechoices-choices/

 

Regards