cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leo_89
Post Prodigy
Post Prodigy

Sum of lasso rectangle selection

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:

 

leo_89_0-1623618324582.png

 

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:

Sum selected hours Power = SUMX(Fact,[Average Power])

 

However, the above measure did not produce the sum of selected hours of visual. Could anyone help me where am I making the mistake?

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @leo_89 ,

 

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

 

 

View solution in original post

2 REPLIES 2
v-deddai1-msft
Community Support
Community Support

Hi @leo_89 ,

 

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

 

 

View solution in original post

parry2k
Super User III
Super User III

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






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.





Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors