Calculating average based on different column.

[Nepal101]

Hello Everyone, 
I hope to get some help on this DAX or any idea on how to solve this issue. Below is just an example of the data. 
I created a column that would do some average 

Avgwithincolumn = Calculate( AVERAGEX('D-with dates','D-with dates'[Avg), ALLEXCEPT('D-with dates','D-with dates'[Customer],'D-with dates'[Year],'D-with dates'[accountnumber],'D-with dates'[group]))
when using this calculation it gives 19 instead of 18 value. so when we add the avg coulmn= 89/5=18 which is the correct value but in the average calculated column I see 19. I am not sure what the reason could be. for other customers the average is correct 
If anyone could help me that would be great.

Customer	salesperson	group	accountnumber	avg	avg calculatedcolumn
A	B	001	23	13	19
A	B	001	23	16	19
A	B	001	23	17	19
A	B	001	23	21	19
A	B	001	23	22	19

[Nepal101]

Hello @Daniel29195  and @v-rzhou-msft 

Thank you for the reply I don't think I have an option to attach the pbix file. Please let me know how can I attach the PBIX file. 
so when I looked at the data again on the data view there were numbers duplicated due to the dates so that was the reason I was getting 19. as there was 39 count of rows and 733 sum = 18.79 so in this case is there a way to calculate average by creating distinct count and distinct sum so the value could be 5 distinct count rows and 89 sum. I hope the below example can be helpful. Please let me know how can I achieve this. The data is such that I can't remove the duplicates I need that for other reports. Thank you again for your time and for looking into this.

Customer	Salesperson	Group	account number	avg	Date
A	B	001	02	13	01/22/2023
A	B	001	02	16	2/22/2023
A	B	001	02	17	4/22/2023
A	B	001	02	21	8/24/2023
A	B	001	02	22	9/22/2023
A	B	001	02	13	01/22/2023
A	B	001	02	16	2/22/2023
A	B	001	02	17	4/22/2023
A	B	001	02	21	8/24/2023
A	B	001	02	22	9/22/2023
A	B	001	02	13	01/22/2023
A	B	001	02	16	2/22/2023
A	B	001	02	17	4/22/2023
A	B	001	02	21	8/24/2023

[Daniel29195 · Accepted solution]

@Nepal101 

var c = table_name[Customer]
var s = table_name[salesperson]
var g = table_name[group]
var an = table_name[account number]

return
averagex ( 
     filter(
        summarize( 
           table_name,
            table_name[Customer],
            table_name[salesperson],
            table_name[group],
            table_name[account number],
           table_name[avg]

        ),
table_name[Customer] = c && 
table_name[salesperson] = s &&
table_name[group] = g &&
table_name[account number] = an 
),

table_name[avg]

)

let me know if it works for you . 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Customer

Salesperson

Group

account number

avg

Date

[Nepal101]

Thank you so much this worked @Daniel29195  

[Nepal101]

Hello @Daniel29195, 
Thank you for the reply. 
I did removed the year but still it is showing me 19  is there anything that I should try. I tried removing each column and running it but no luck 

[v-rzhou-msft]

Hi @Nepal101 ,

I have a test by code as below. 

My Code = 
CALCULATE (
    AVERAGEX ( 'D-with dates', 'D-with dates'[Avg] ),
    ALLEXCEPT (
        'D-with dates',
        'D-with dates'[Customer],
        'D-with dates'[accountnumber],
        'D-with dates'[group]
    )
)

I create a measure and a calculated column to have a test.

I get the same result as  Daniel29195, please share a sample with us, this will make it eaiser for us the find the solution.

You also can download my attatchment to compare my data model with yours.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daniel29195]

@Nepal101 

can you share your file so i can take a look ? 
best regards

[Daniel29195]

@Nepal101 

i just removed the year in allexcept . 

im not sure if this is what you want . but it returns the desired number .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠