Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have a live connection dataset and I need to create a small measure to calculate ratio's.
The dataset is as below, It is a 15 min interval dataset
Timestamp Value
08-08-2020 2:00 34
08-08-2020 2:15 12
08-08-2020 2:30 15
08-08-2020 2:45 3
08-08-2020 3:00 3
08-08-2020 3:15 2
08-08-2020 3:30 11
I need to calculate the ratio of current value with the first 3 values of previous hour timestamps. For ex, if I am at 3: 15, it should take the avg of first 3 values of previous hour (2:00, 2:15, 2:30). avg=(34+12+15)/3
So the result should be (2/avg)
Similarly if the 3:30 timestamp comes in, the result should be 3/avg
Thanks
Solved! Go to Solution.
I calculated the sum of previous 3 values, so you want to average it like this? You did not say if you have more than 1 day or if your data is well structured, the measure needs to adjust accordingly.
@pbidemouser2 - If you are trying to compare rows, see my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
When 3:30 came in, should be 11/avg, right? Your dataset always has :00, :15, :30, :45, total 4 values? And you have more than 1 day values? How to deal with no previous timestamp? I did a simple one based on your sample, as yours is live connection, so only can go with measures:
Hi, @Vera_33
I'm not sure how you calculated this, but the ratio seems wrong.
The ratio at 3:00 should be = 3 / (avg of 34,12,15)
The ratio at 3:15 should be = 2 / (avg of 34,12,15)
I calculated the sum of previous 3 values, so you want to average it like this? You did not say if you have more than 1 day or if your data is well structured, the measure needs to adjust accordingly.
@pbidemouser2
I need a clarification:
If your current value is 3:15 then you need to average 2:15, 2:30 & 2:45 values then divide it by 3:15 value right?
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
If the current value is at 3:15, then i need to take the avg of 2:00, 2:15, 2:30.Lets call it as 'prev_avg'. Final value should be current value at 3:15 divided by prev_avg.
Similarly for 3:30, prev_avg will be the same but result is current value of 3:30 divided by prev_avg
@pbidemouser2
Try this Measure: I can add validation in the measure to show blank for the first few rows if you need.
Average First 3 PRev Hour =
var cd = SELECTEDVALUE(TIMEDATA[TimeStamp])
var ch = CONVERT(INT(cd) + (HOUR(cd)-1)/24,DATETIME)
var t =
SUMX(
TOPN(3,
FILTER(
ALL(TIMEDATA),
TIMEDATA[TimeStamp] >= ch),
[TimeStamp],ASC,TIMEDATA[TimeStamp]),
[Value]
)
return
DIVIDE(
SELECTEDVALUE(TIMEDATA[Value]),
t
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@pbidemouser2
In that case, @Vera_33's solution should work for you.
Let us know if you have difficulty implementing it.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |