Summing the measure

Question

Hi there,&nbsp;I have data which looks like the following table:&nbsp;IncidentTimeNumberOfConsumersInterruptionDurationTotalConsumerMinutesEpoch Half Hour NumberSaturday, 4 April 202019700355191Sunday, 5 April 20200970355201Sunday, 5 April 202069171173355214Sunday, 5 April 20208435229568355202Sunday, 5 April 202015300355202Sunday, 5 April 20206933423046355202Monday, 6 April 20201232232355266Thursday, 9 April 20208911310057355430Thursday, 9 April 2020171332261355430Thursday, 9 April 202078967488355426Monday, 13 April 2020558290355605Wednesday, 15 April 20200220355681Monday, 6 April 2020287174355274Monday, 6 April 20201315315355267Monday, 20 April 2020012030355923Monday, 20 April 20201759810166355943Thursday, 23 April 2020080356110Thursday, 23 April 20204400356110Thursday, 23 April 202026881376356110Wednesday, 29 April 2020781239594356382Wednesday, 29 April 2020513105130356382Wednesday, 29 April 20205132613338356385Tuesday, 7 April 20203354515075355331Tuesday, 14 April 202045011149950355660Tuesday, 14 April 202020412124684355660Tuesday, 14 April 2020434172355653Tuesday, 14 April 202044472068355660Tuesday, 14 April 2020312343702355660Tuesday, 14 April 20200170355659Tuesday, 14 April 20200170355658Tuesday, 14 April 2020651100355659Tuesday, 14 April 20209832019660355659Friday, 24 April 20204411441356153Tuesday, 28 April 202044300356349Monday, 4 May 202073902730356610Monday, 4 May 2020243598616356610Tuesday, 5 May 2020129364644356667Tuesday, 5 May 202042391638356675Tuesday, 5 May 2020251192975356677Tuesday, 5 May 2020654260356684Tuesday, 5 May 202061741044356677Tuesday, 5 May 2020652130356685Tuesday, 5 May 2020133053965356677Tuesday, 5 May 2020341615474356677Tuesday, 5 May 2020527364356663Tuesday, 5 May 202014541259740356660Tuesday, 5 May 202052733796356660Tuesday, 5 May 20206041324780356660Tuesday, 5 May 202013812817664356660Tuesday, 5 May 2020311133503356660Tuesday, 5 May 20206048350132356660Wednesday, 6 May 202002990356706Wednesday, 6 May 2020214379177356706Wednesday, 6 May 20202444888356706Wednesday, 6 May 20202280560356707Friday, 8 May 202012600356785Saturday, 9 May 2020401234920356850Tuesday, 5 May 2020132953835356658Tuesday, 5 May 2020101851850356659Tuesday, 12 May 2020451637335356999Tuesday, 12 May 20201922418356999Wednesday, 13 May 2020252807000357049Wednesday, 13 May 2020342428228357049Wednesday, 13 May 20206762818928357049Thursday, 14 May 20201418252357092Thursday, 14 May 2020152513765357086Thursday, 14 May 2020149126357094Thursday, 14 May 202064042560357093Thursday, 14 May 202042184357093Thursday, 14 May 202064042560357093Wednesday, 6 May 20207473526145356705Thursday, 14 May 2020018900357088Thursday, 14 May 2020248357087Thursday, 14 May 20202816357100Tuesday, 19 May 20201428428357331Wednesday, 20 May 20200320357406Wednesday, 20 May 20206000357368Wednesday, 20 May 2020164216736357368Wednesday, 20 May 202027493493466357373Wednesday, 20 May 20200220357390&nbsp;&nbsp;Epoch Half Hour Number in the table shows the half an hour interval.&nbsp;I want to create four measures to perform calculations. In the first measure (which looks like below) calculates the raw value happened in half an hour time period:first_measre_30mins =&nbsp;var Time30Min = MAX('Table'[Epoch Half Hour Number])ReturnCALCULATE ( SUMX(FILTER(filter(ALL('Table'),'Table'[Epoch Half Hour Number] = Time30Min),table[TotalConsumerMinutes] &gt;= 0,'Outages and Interruptions'[TotalConsumerMinutes]))/90000&nbsp;In the second measure, I do the same calculation but for last 24 hrs. My second measure looks like following&nbsp;Second_measure_24hrs =&nbsp;var Time30Min = MAX('Table'[Epoch Half Hour Number])ReturnCALCULATE ( SUMX(FILTER(filter(ALL('Table'),'Table'[Epoch Half Hour Number] &lt;= Time30Min &amp;&amp; 'Table'[Epoch Half Hour Number] &gt;= Time30Min-47),Table[TotalConsumerMinutes]&gt;= 0),'Table'[TotalConsumerMinutes]))/90000&nbsp;In third measure, I normalize the value calculated in the first measure. So my third measure looks like:Normalize_first_measure_30mins =&nbsp;IF([Second_measure_24hrs ] &gt; 5.69 &amp;&amp; [first_measre_30mins ] &gt; 5.69/48,5.69/48,[first_measre_30mins ])&nbsp;Now in the fourth measure, I want to sum up third measure (which is normalise value of 30 mins) in such a way that it sum upall third measure values occurred in the last 24 hours.&nbsp;&nbsp;I am a bit lost now and could not figure out how to do that. Could anyone help me with that?&nbsp;&nbsp;

amitchandak · Answer

Dunner2020 , not very clear to me but you can create a time table and join time part to it.
https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
&nbsp;
Time&nbsp; = [Datetime].time
&nbsp;
You can create various date part and bucket in time table and use that

v-xuding-msft · Answer

Hi&nbsp;Dunner2020&nbsp;,
&nbsp;
I'm not clear about the logic of the sample data and measures either. But if you want to summarize&nbsp;the third measure, you could try like below and change&nbsp;'Table' to Filter(table,&nbsp; expressions)&nbsp; to add filter conditions of last 24 hours.&nbsp;
Measure = SUMX('Table',[Normalize_first_measure_30mins])
&nbsp;

If you still can't solve it, please share the computing logic and your expected result. Thanks!
&nbsp;
&nbsp;
&nbsp;

v-xuding-msft · Answer

Hi&nbsp;Dunner2020&nbsp;,
&nbsp;
Could you tell me if your problem has been solved? If it is, kindly share your solutions. More people who encounter the same issue will benefit from here. If you are still confused about it, please share more details to us.&nbsp;
&nbsp;
&nbsp;

Forum Discussion

Summing the measure

3 Replies

Related content

Measure to sum MTD for ALL months

Summing a measure

Rolling SUM / Cumulative SUM for measure

Average of Sum Measure

Running sum

IncidentTime	NumberOfConsumers	InterruptionDuration	TotalConsumerMinutes	Epoch Half Hour Number
Saturday, 4 April 2020	197	0	0	355191
Sunday, 5 April 2020	0	97	0	355201
Sunday, 5 April 2020	69	17	1173	355214
Sunday, 5 April 2020	84	352	29568	355202
Sunday, 5 April 2020	153	0	0	355202
Sunday, 5 April 2020	69	334	23046	355202
Monday, 6 April 2020	1	232	232	355266
Thursday, 9 April 2020	89	113	10057	355430
Thursday, 9 April 2020	17	133	2261	355430
Thursday, 9 April 2020	78	96	7488	355426
Monday, 13 April 2020	5	58	290	355605
Wednesday, 15 April 2020	0	22	0	355681
Monday, 6 April 2020	2	87	174	355274
Monday, 6 April 2020	1	315	315	355267
Monday, 20 April 2020	0	1203	0	355923
Monday, 20 April 2020	17	598	10166	355943
Thursday, 23 April 2020	0	8	0	356110
Thursday, 23 April 2020	44	0	0	356110
Thursday, 23 April 2020	2	688	1376	356110
Wednesday, 29 April 2020	78	123	9594	356382
Wednesday, 29 April 2020	513	10	5130	356382
Wednesday, 29 April 2020	513	26	13338	356385
Tuesday, 7 April 2020	335	45	15075	355331
Tuesday, 14 April 2020	450	111	49950	355660
Tuesday, 14 April 2020	204	121	24684	355660
Tuesday, 14 April 2020	43	4	172	355653
Tuesday, 14 April 2020	44	47	2068	355660
Tuesday, 14 April 2020	3	1234	3702	355660
Tuesday, 14 April 2020	0	17	0	355659
Tuesday, 14 April 2020	0	17	0	355658
Tuesday, 14 April 2020	6511	0	0	355659
Tuesday, 14 April 2020	983	20	19660	355659
Friday, 24 April 2020	441	1	441	356153
Tuesday, 28 April 2020	443	0	0	356349
Monday, 4 May 2020	7	390	2730	356610
Monday, 4 May 2020	24	359	8616	356610
Tuesday, 5 May 2020	129	36	4644	356667
Tuesday, 5 May 2020	42	39	1638	356675
Tuesday, 5 May 2020	25	119	2975	356677
Tuesday, 5 May 2020	65	4	260	356684
Tuesday, 5 May 2020	6	174	1044	356677
Tuesday, 5 May 2020	65	2	130	356685
Tuesday, 5 May 2020	13	305	3965	356677
Tuesday, 5 May 2020	34	161	5474	356677
Tuesday, 5 May 2020	52	7	364	356663
Tuesday, 5 May 2020	145	412	59740	356660
Tuesday, 5 May 2020	52	73	3796	356660
Tuesday, 5 May 2020	60	413	24780	356660
Tuesday, 5 May 2020	138	128	17664	356660
Tuesday, 5 May 2020	31	113	3503	356660
Tuesday, 5 May 2020	604	83	50132	356660
Wednesday, 6 May 2020	0	299	0	356706
Wednesday, 6 May 2020	21	437	9177	356706
Wednesday, 6 May 2020	2	444	888	356706
Wednesday, 6 May 2020	2	280	560	356707
Friday, 8 May 2020	126	0	0	356785
Saturday, 9 May 2020	40	123	4920	356850
Tuesday, 5 May 2020	13	295	3835	356658
Tuesday, 5 May 2020	10	185	1850	356659
Tuesday, 12 May 2020	45	163	7335	356999
Tuesday, 12 May 2020	19	22	418	356999
Wednesday, 13 May 2020	25	280	7000	357049
Wednesday, 13 May 2020	34	242	8228	357049
Wednesday, 13 May 2020	676	28	18928	357049
Thursday, 14 May 2020	14	18	252	357092
Thursday, 14 May 2020	15	251	3765	357086
Thursday, 14 May 2020	14	9	126	357094
Thursday, 14 May 2020	640	4	2560	357093
Thursday, 14 May 2020	4	21	84	357093
Thursday, 14 May 2020	640	4	2560	357093
Wednesday, 6 May 2020	747	35	26145	356705
Thursday, 14 May 2020	0	1890	0	357088
Thursday, 14 May 2020	2	4	8	357087
Thursday, 14 May 2020	2	8	16	357100
Tuesday, 19 May 2020	1	428	428	357331
Wednesday, 20 May 2020	0	32	0	357406
Wednesday, 20 May 2020	60	0	0	357368
Wednesday, 20 May 2020	16	421	6736	357368
Wednesday, 20 May 2020	2749	34	93466	357373
Wednesday, 20 May 2020	0	22	0	357390