Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello everyone, I am new to Power BI. We used Postgres data from our chatbot system.
So this is our chatbot result. I would like to calculate % change with line value showing how many % of user drop/jump in each day.
Can anyone help with me.
Database is like that
Hi @zawlh
please, provide your desired output more accurace.
what is "how many % of user drop/jump in each day" ?
what is the total users? total by day, total cummulative or smth? How exactly calculate it? maybe you can display an example
where is users field in your data model?
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38 the number is the distinctcount of messengerID. I want percent difference from previous column(column from chart). (For eg. 38% drop from day01-begin to day01-end (assuming day01-begin is 100%). Could you solve it ?
its still not almost clear but try to create a measure like
Measure =
var _prevDayNumberStart = CALCULATE(MAX('Table'[Day Number]), FILTER(ALL('Table'), 'Table'[timestamp] < SELECTEDVALUE('Table'[timestamp]) && CONTAINSSTRING('Table'[Day Number],"begin"))
var _prevDayTimestamp = CALCULATE(MAX('Table'[timestamp]), 'Table'[Day Number]=_prevDayNumberStart)
RETURN
DIVIDE( CALCULATE(DISTINCTCOUNT('Table'[messenger_id]), FILTER(ALL('Table'), 'Table'[timestamp] < SELECTEDVALUE('Table'[timestamp])), CALCULATE(DISTINCTCOUNT('Table'[messenger_id]), FILTER(ALL('Table'), 'Table'[timestamp] < _prevDayTimestamp) )
not sure, it will work, need to check
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38 I will show you database
I cannot use the formula you provide unless you don't know exactly about my data
sorry for confidential data
@az38 for each day. And I want to see how many percent of users have dropped. Day Number is just alternate name of attribute. There are too many attribute in chatbot. But I only need some attribute and I have to change the name with another column
@az38 I used Day Number is X-axis and DISTINCTCOUNT of messenger_id in value(so not difficult). What makes me difficult is the percent change in each day
The above graph is the example
try a measure like this
Measure =
var _currentContextDay = DATE(YEAR(SELECTEDVALUE('Table'[timestamp]));MONTH(SELECTEDVALUE('Table'[timestamp]));DAY(SELECTEDVALUE('Table'[timestamp])))
var _countCurDay = CALCULATE(DISTINCTCOUNT('Table'[messenger_id]);FILTER(ALL('Table');'Table'[timestamp].[Date]=_currentContextDay))
var _countLastDay = CALCULATE(DISTINCTCOUNT('Table'[messenger_id]);FILTER(ALL('Table');_currentContextDay=DATEADD('Table'[timestamp].[Date];1;DAY)))
return
1-DIVIDE(_countLastDay;_countCurDay)
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38 I am sorry. Because of my bad English writing, I think you misunderstand something. I want X-axis to be Day Number(day01-begin,day01-end,day02-begin,.....). The percent change I want is how many percent of users have dropped from day01 to day02 to day03 to ........
Thanks for your patience and guiding.
I hope you can help me with this.
look here https://ufile.io/lvak35gl
simple example which looks like you need (as I think)
do not hesitate to give a kudo to useful posts and mark solutions as solution
@az38 Bro your formula makes some progress. But I still have some problems. Could you help with me please. My measure show only 0%
.
I used the same exact formula with yours, using my original table name.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
41 | |
32 | |
23 | |
23 | |
22 |