Forum Discussion
Avg Balance DAX
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
8 Replies
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/1447523please see my new reply
Hi,
Please explain how you arrived at the ansers of 98 and 156.
please see my new reply
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)
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_/_DAYSIf 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.
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)
