Forum Discussion
Average of Two columns by adding the second Column First row to the First Column
Hi Team,
I tried differet ways to get Average for the below data but was not possible to get the Dax foumula. Could some one help me in cracking this problem.
I have two columns named Checking as First column and First date Min as Second Column.
Problem - I need to get average by considering the First date Min column first row data in Checking column
Below is the example
It need to get average as below
Hi,
Thanks for the solution freginier and MFelix offered, and i want to offer some more information for user to refer to.
hello Anonymous , you can refer to the follwing solution.
Sample data:
Create a measure
MEASURE = VAR a = CALCULATETABLE ( DISTINCT ( 'Table'[First date min] ), FILTER ( ALLSELECTED ( 'Table' ), [End MY] IN VALUES ( 'Table'[End MY] ) ) ) VAR b = SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), [End MY] IN VALUES ( 'Table'[End MY] ) ), "Test", [Chencking] ) RETURN AVERAGEX ( UNION ( a, b ), [First date min] )Then create a measure and put the following field to it.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- MFelix
Super User
Hi Anonymous ,
How do you have this two columns in terms of the model? Do you only want to get the average value or do you also need to get the lines as you show in the image?
Can you give a litle bit more context on the way the data is setup please.
freginierSolution Sage
Hey there!
To calculate the average for the dataset, try using this DAX function: AverageValue =
VAR FirstRowValue = FIRSTNONBLANK( TableName[Checking], 1 )
VAR TotalSum = SUM( TableName[Checking] ) + FirstRowValue
VAR TotalCount = COUNT( TableName[Checking] ) + 1
RETURN
TotalSum / TotalCountHope it works!
Zoe 😁😁
MFelix freginier Thank you so much for your reply .. I just need the Average as below.. there is one more coulmn which indicates the month as End MY as shown below. The average needs to consider based on month. Giving you 3 examples
Jan Month
Should receive average as below
December Month
Should be
November Month
Hi,
Thanks for the solution freginier and MFelix offered, and i want to offer some more information for user to refer to.
hello Anonymous , you can refer to the follwing solution.
Sample data:
Create a measure
MEASURE = VAR a = CALCULATETABLE ( DISTINCT ( 'Table'[First date min] ), FILTER ( ALLSELECTED ( 'Table' ), [End MY] IN VALUES ( 'Table'[End MY] ) ) ) VAR b = SELECTCOLUMNS ( FILTER ( ALLSELECTED ( 'Table' ), [End MY] IN VALUES ( 'Table'[End MY] ) ), "Test", [Chencking] ) RETURN AVERAGEX ( UNION ( a, b ), [First date min] )Then create a measure and put the following field to it.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
