Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Solved! Go to Solution.
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.
Avg DIO Year-End = AVERAGEX ( Actual, [DIO Year-End] )
Regards
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.
http://exceleratorbi.com.au/sum-vs-sumx-in-dax/
https://www.powerpivotpro.com/2014/10/sum-sumx-or-calculatechoices-choices/
Regards
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.
Avg DIO Year-End = AVERAGEX ( Actual, [DIO Year-End] )
Regards
Can you share your calculated column expression?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
It's the same thing
DIO Year-End = ([Net Inventory Month End]*91/[Total COS (Adj.) 3 Mths])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |