Forum Discussion
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)
Total COS (Adj.) 3 Mths := CALCULATE([Amount]),DATESBETWEEN(DateTable[EndOfMonth],LASTDATE(DateTable[EndOfMonth - 2 Mths]),LASTDATE(DateTable[EndOfMonth]))
It is working but I needed to make an average out of this thus I created a calculated column.
The problem is that the calculated column returns a 0 value for each row in comparison to the measure which returns actual numbers.
Can anyone help me ?
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
6 Replies
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
Can you share your calculated column expression?
It's the same thing
DIO Year-End = ([Net Inventory Month End]*91/[Total COS (Adj.) 3 Mths])
Cool,
1. Create the following measure in column .
Total COS (Adj.) 3 Mths := CALCULATE([Amount]),
DATESBETWEEN( DateTable[EndOfMonth],
LASTDATE(DateTable[EndOfMonth - 2 Mths]),LASTDATE(DateTable[EndOfMonth]))
if it is working fine then try another measure in column :
Net Inventory Month End := CALCULATE(SUM(Actual[Amount]),Actual[ID]=1)
why am saying this then only u can know the prob where it is arise.
if everythis is working fine then try the final formula,
let me know where is the prob then i will help .
if u share some sample data from my end also try. cheers....
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
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
