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 there,
I got a table (fact table) that has over 10 million rows. The table contains the half-hour value of power loss. I created a measure that calculates the average of power loss and displays it line chart visual. The measure looks as follow:
Average Power = Average (Fact[Power])
The x-axis contains the hour of the day (starting from 0 to 23). Hour data is coming from time table (dimension table). The visual looks as follow:
My first question is:
As I calculated the average power based on half-hour value and now displaying it against the hour. Can we say that visual shows hourly average power value?
I enabled the rectangle lasso selection. I want to select the multiple hours from the line graph and create a new measure that displays the sum of those selected hours. In other words, the new measure shows the sum of the average power of selected hours.
I created a measure as follow:
However, the above measure did not produce the sum of selected hours of visual. Could anyone help me where am I making the mistake?
Solved! Go to Solution.
Hi @Dunner2020 ,
>>As I calculated the average power based on half-hour value and now displaying it against the hour. Can we say that visual shows hourly average power value?
If your time dimension table don't contains half hours value. It will calculate hourly average, and in your situation, it is the same as sum value of that hour.
If you need to calculate average value in every hour, please add a new hour column in your fact table like : Hour = Hour(Fact[Time]), then connect fact table with the dimension table by the new hour column.
Then you can use the following measure to calculate sum of the average power of selected hours.
Sum selected hours Power = SUMX(Values(TimeDimensionTale[Hour]),[Average Power])
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @Dunner2020 ,
>>As I calculated the average power based on half-hour value and now displaying it against the hour. Can we say that visual shows hourly average power value?
If your time dimension table don't contains half hours value. It will calculate hourly average, and in your situation, it is the same as sum value of that hour.
If you need to calculate average value in every hour, please add a new hour column in your fact table like : Hour = Hour(Fact[Time]), then connect fact table with the dimension table by the new hour column.
Then you can use the following measure to calculate sum of the average power of selected hours.
Sum selected hours Power = SUMX(Values(TimeDimensionTale[Hour]),[Average Power])
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Dunner2020 yes whatever granularity you are looking at the average will be calculated at that granular, in this case, if you are viewing data per hour, you are seeing an hourly average.
Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |