How to calculate total based on time period slicer

[icrishanka]

Hi ,

Please support on following (I have tried different methods, but it is not workable).

As per following tables, 
I want to group and get total values 1 hourly, 6 hourly, 8 hourly,... 

i.e : 12:00:00 AM - 12:59:00 AM, there are two data points(5,6) for that day (28). I want, if I click 1 hour (select), then it is group to 12:00:00 AM with total 11 for that day. Like wise I need to calculate.   

My data comes from realtime from server(SQL) with large data base in daily basis.

If someone give a solution, it is great support. 

Thank you

[Anonymous]

Hi @icrishanka ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a slicer table.

SlicerTable = GENERATESERIES(1,7,1)

(3) We can create measures. 

Measure = 
var _a=IF(HOUR(MAX('Table'[Time]))=12,0,HOUR(MAX('Table'[Time])))
var _b=SELECTEDVALUE('SlicerTable'[Value])
return ROUNDDOWN(DIVIDE(_a,_b),0)

Measure 2 = 
var _a=[Measure]
var tmp=FILTER(ALL('Table'),[Measure]=_a)
var _b=MINX(tmp,[Time])
var cur_time=SELECTEDVALUE('Table'[Time])
return
IF(cur_time=_b,1)

Measure 3 = 
var _a=[Measure]
var tmp=FILTER(ALL('Table'),[Measure]=_a)
return
SUMX(tmp,[Value])

_New_Time = 
var _a=IF(HOUR(MAX('Table'[Time]))=0,12,HOUR(MAX('Table'[Time])))
return  TIMEVALUE(_a & ":"& "00:00" &" " &RIGHT(MAX('Table'[Time]),2))

(4) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[icrishanka]

Hi Neeko Tang,

Thank you. This is very helpful.

But, when I work with real data, I can't get expected result as sample given by you. 

** my data set contained more than 10 varaiables.  And user can select date range (based on Calander table, link with other table)

May be some issue in Measure 2, 

My ultimate goal is, Calculate Retect Rate = (Error/(Error+Good)) based on the date, time (1hour, 4hour, 8hour using slicer).. And create that line graph for Reject Rate vs (Date + New_time)

Thanks!

Chanaka

[Anonymous · Accepted solution]

Hi @icrishanka ,

I retested with your data and the output of the measure is correct, please check your data type and data model.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[icrishanka]

Hi Neeko Tang,

Yes, your code is correct. But I seen when it select multiple dates,  we didn't get result correctly. 

At the same time, my data set is contained multiple columnes, not only Date, Time and Value. 

Can you help me to verify above two adding two cases? I am trying to amend codes, but not workable. 

Example : you can select few multiple dates and add few columns. 

Thank you. 
  

[Anonymous]

Hi @icrishanka ,

In order to solve your new problem , please create a new case, we suggest a case to solve only one problem, because it can get good help and give a better reference for other users!

Best Regards,

Neeko Tang