Formula to count distinct customers and other formula isnt adding up right

[Reagorn]

My rows are categories, and has a lower heirchy for customers in those categories. 

My first question is, how can I make a formula to do what "count of acctnumber" is doing? I basically put AcctNumber is my values and changed it to count. I need to have an average cost per category by doing Total Cost/Customer count. I'm not sure if Total Cost/Count of AccNumber will give me what I need. 

My second question is for LOS. For some reason, when there is one customer is the category, it gives me the right total LOS, but when there's alot of customers, like the one that has 4 (it should be 56+42+38+etc), is not giving me the right thing.

This is my formula for LOS

LOS =

Var CurrentName = SELECTEDVALUE( 'Query1'[AcctNumber] )

Return

MAXX(

FILTER(ALL('Query1'), 'Query1'[AcctNumber] = CurrentName ),

Query1[LOS_Days])

Any advice? Thanks

[v-jianboli-msft · Accepted solution]

Hi @Reagorn ,

Based on your description, I have created a simple sample:

Please try:

Average cost = [Total Cost]/COUNT(Query1[AcctNumber])

LOS = 
var CurrentName = SELECTCOLUMNS('Query1',"Name",[AcctNumber])
var _Table = SUMMARIZE(FILTER(ALL(Query1),[AcctNumber] in CurrentName),Query1[AcctNumber],"Value",MAX('Query1'[LOS_Days]))
Return SUMX(_Table,[Value])

Final output:

Best Regards,

Jianbo Li

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