Sum of lasso rectangle selection

[Dunner2020]

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:

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?

[v-deddai1-msft · Accepted 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

[parry2k]

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