Avg Balance DAX

[tarolz132q]

Hi Folks,

I would like to measure the Avg coin balance for each day.

The challenge is to calculate only the newst coin balance for each date by USER_ID. if you notice, AF4FF96..... is shown twice! then when 05/26 is being calculated should be ignore the previous values.

The result I would like to see that for the 05/25/22 - 98

The result I would like to see that for the 05/26/22 - 156

[tarolz132q]

Hi Guys, 

ill try to be more specific.

I would like to know what is the Coin balance in each time datapoint on my timeline.

It mean that for every date I would like to sum the last coin balance for each user_id untill that specific date and then divide by the number of players.

The outcome will be chart like that (disregard the values):

The table is created in a way that in each date user id can appear only once!

my table:

DATE	USER_ID	COIN_BALANCE
6/1/2022	572EDE3F07F53CDD	800
6/1/2022	7FBF5E6AC1126381	1
6/1/2022	CB086763FC1E3CC6	4
6/1/2022	CE471B4692E6B8ED	120
6/1/2022	F40C6A78878151B3	92
6/2/2022	233E855015CD3431	69
6/2/2022	73703E52FAD56095	66
6/2/2022	A6499BEE8D6C8A40	48
6/2/2022	CB086763FC1E3CC6	9
6/2/2022	CE471B4692E6B8ED	135

The measure results should be like that:

For day 1 it's easy calculation because I have 5 different users, i just sum up all 1017/5 = 203.4

For day 2 the calculation should include users that might be online in day 1 as well, but I want to take the last coin balance for each user_id then : 1220/8 = 152.5 (8 Different users included, 3 from day 1 and 5 from day 2)

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi @tarolz132q ,

How to get the 05/26/22 - 156?

Create a measure.

Measure = VAR _DAYS= COUNTAX(FILTER(ALL('Table'),'Table'[date]=SELECTEDVALUE('Table'[date])),'Table'[date])
VAR SUM_=CALCULATE(SUM('Table'[COIN_BALANCE]),FILTER(ALL('Table'),'Table'[date]=SELECTEDVALUE('Table'[date])))
RETURN
SUM_/_DAYS

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

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

[Ashish_Mathur]

Hi,

Please explain how you arrived at the ansers of 98 and 156.

[tarolz132q]

please see my new reply

[lbendlin]

Avg = 
var d = SELECTEDVALUE('Table'[DATE])
var a = calculatetable(SUMMARIZE('Table','Table'[USER_ID],"md",max('Table'[DATE])),'Table'[DATE]<=d)
var b = ADDCOLUMNS(a,"mb",var md=[md] return CALCULATE(sum('Table'[COIN_BALANCE]),'Table'[DATE]=md))
return  divide(sumx(b,[mb]),countrows(b),0)

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[tarolz132q]

please see my new reply