Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
zawlh
Helper I
Helper I

Calculate % change from previous column

Hello everyone, I am new to Power BI. We used Postgres data from our chatbot system. image.png

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
Screenshot (17).png

12 REPLIES 12
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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 ?

az38
Community Champion
Community Champion

@zawlh 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38  I will show you database
I cannot use the formula you provide unless you don't know exactly about my data Screenshot (19)_LI.jpgScreenshot (21)_LI.jpg

sorry for confidential data

az38
Community Champion
Community Champion

@zawlh 

distinct count of users should be calculate for a day or for the whole period before the day?

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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
Community Champion
Community Champion

@zawlh 

and how do you calculate count of users in day-begin point?

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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
2020-01-29-16-21-docs.google.com.png

The above graph is the example

az38
Community Champion
Community Champion

@zawlh 

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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.

az38
Community Champion
Community Champion

@zawlh 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38  Bro your formula makes some progress. But I still have some problems. Could you help with me please. My measure show only 0%
Screenshot (21).png
I used the same exact formula with yours, using my original table name. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.