Average of Two columns by adding the second Column First row to the First Column

[manubk]

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 

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution freginier  and MFelix  offered, and i want to offer some more information for user to refer to.

hello @manubk , 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.

[manubk]

@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 

 

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution freginier  and MFelix  offered, and i want to offer some more information for user to refer to.

hello @manubk , 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.

[manubk · Accepted solution]

@v-xinruzhu-msft  Thank you soo much.. This is how i wanted the soultion.. Big kudos to you 

[freginier]

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 / TotalCount

Hope it works!

Zoe 😁😁

[MFelix]

Hi @manubk ,

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.