Forum Discussion
Additional filter into measure
Hello everybody,
I have a table of values
ID | CounterNumber | ReqDateT | DataDateT | SummAPlus |
68511 | 139 | 2018-02-06 17:14:45.000 | 2018-02-05 00:00:00.000 | 11632,8 |
68512 | 139 | 2018-02-06 17:14:54.000 | 2018-02-05 00:00:00.000 | 11632,8 |
68529 | 149 | 2018-02-06 17:17:16.000 | 2018-02-05 00:00:00.000 | 1075 |
68530 | 149 | 2018-02-06 17:17:20.000 | 2018-02-05 00:00:00.000 | 1075 |
68599 | 1 | 2018-02-06 17:22:51.000 | 2018-02-05 00:00:00.000 | 4933,6 |
68600 | 1 | 2018-02-06 17:22:55.000 | 2018-02-05 00:00:00.000 | 4933,6 |
68629 | 139 | 2018-02-06 21:00:16.000 | 2018-02-05 00:00:00.000 | 11632,8 |
68638 | 149 | 2018-02-06 21:02:23.000 | 2018-02-05 00:00:00.000 | 1075 |
68673 | 1 | 2018-02-06 21:06:54.000 | 2018-02-05 00:00:00.000 | 4933,6 |
68159 | 139 | 2018-02-05 07:00:24.000 | 2018-02-04 00:00:00.000 | 14162,4 |
68168 | 149 | 2018-02-05 07:02:31.000 | 2018-02-04 00:00:00.000 | 926,8 |
68203 | 1 | 2018-02-05 07:07:43.000 | 2018-02-04 00:00:00.000 | 5631,6 |
I need to select max value of SummAPlus for the each of CounterNumber by DataDateT
I can do it by:
ConsumedEnergy D = Max(PowerCountersPreviousDay[SummAPlus])
sumdeltaD = SUMX(FILTER(ALLSELECTED(PowerCountersPreviousDay[DataDateT]),lastdate(PowerCountersPreviousDay[DataDateT])),PowerCountersPreviousDay[ConsumedEnergy D])
and now I need to summarize consumption by several CounterNumbers, and I don't understand how to do it.
I will appreciate any help. Thanks
Hi dkushner,
From the description of your replay, you should create a calculated table first in the formula below to get the max value of SummAPlus for the each of CounterNumber by DataDateT.
Table = SUMMARIZE ( PowerCountersPreviousDay, PowerCountersPreviousDay[DataDateT], PowerCountersPreviousDay[CounterNumber], "Sumsingle", MAX ( 'PowerCountersPreviousDay'[SummAPlus] ) )Then, you could the measure :
Bussiness Unit = VAR count1sum = CALCULATE ( SUM ( 'Table'[Sumsingle] ), FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 1 ) ) VAR count139sum = CALCULATE ( SUM ( 'Table'[Sumsingle] ), FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 139 ) ) VAR count149sum = CALCULATE ( SUM ( 'Table'[Sumsingle] ), FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 149 ) ) RETURN count139sum + count1sum - count149sumThis picture of the result is below:
For more details, you could refer to this pbix file.
Hope it can help you!
Best Regards,
Cherry
5 Replies
Hi dkushner，
If I understand your requirement correctly, you could create group with New Group to summarize consumption by several CounterNumbers. As the picture below:
For how to create the group, you could refer to this article.
For more details about the result picture above, you could refer to my pbix file here.
Additionally, you could provide your expectant result demo that I could help you create the visual.
In your convenience, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Dear Cherry,
Thank you very much for your help, but grouping is not exactly what I need. Or may be I don’t understand how to use it in my case ))
Let me explain my task a little bit wider:
I have a table with metering values of power counters (now I am talking about the table PowerCountersPreviousDay)
In this table there are metering values for the previous period and this data are repeated several times, for example:
ID
CounterNumber
ReqDateT
DataDateT
SummAPlus
SummAMinus
SummRPlus
SummRMinus
TransformationCoef
69439
1
2018-02-10 11:07:13.000
2018-02-09 00:00:00.000
7526
-1
5651,6
-1
400
69498
1
2018-02-10 15:07:08.000
2018-02-09 00:00:00.000
7526
-1
5651,6
-1
400
69734
1
2018-02-11 01:08:14.000
2018-02-10 00:00:00.000
4370,8
-1
3270
-1
400
69837
1
2018-02-11 17:25:38.000
2018-02-10 00:00:00.000
4370,8
-1
3270
-1
400
69838
1
2018-02-11 17:25:42.000
2018-02-10 00:00:00.000
4370,8
-1
3270
-1
400
69955
1
2018-02-12 17:26:05.000
2018-02-11 00:00:00.000
6194,8
-1
5055,2
-1
400
69956
1
2018-02-12 17:26:09.000
2018-02-11 00:00:00.000
6194,8
-1
5055,2
-1
400
70029
1
2018-02-12 23:07:26.000
2018-02-11 00:00:00.000
6194,8
-1
5055,2
-1
400
70088
1
2018-02-13 02:06:09.000
2018-02-12 00:00:00.000
4372,8
-1
3777,6
-1
400
69395
139
2018-02-10 11:00:20.000
2018-02-09 00:00:00.000
15817,8
-1
7865,4
-1
600
69454
139
2018-02-10 15:00:20.000
2018-02-09 00:00:00.000
15817,8
-1
7865,4
-1
600
69513
139
2018-02-10 17:16:17.000
2018-02-09 00:00:00.000
15817,8
-1
7865,4
-1
600
69514
139
2018-02-10 17:16:26.000
2018-02-09 00:00:00.000
15817,8
-1
7865,4
-1
600
69690
139
2018-02-11 01:00:18.000
2018-02-10 00:00:00.000
14913,6
-1
7365
-1
600
69749
139
2018-02-11 17:17:17.000
2018-02-10 00:00:00.000
14913,6
-1
7365
-1
600
69750
139
2018-02-11 17:17:26.000
2018-02-10 00:00:00.000
14913,6
-1
7365
-1
600
69867
139
2018-02-12 17:17:39.000
2018-02-11 00:00:00.000
5952
-1
3001,2
-1
600
69868
139
2018-02-12 17:17:47.000
2018-02-11 00:00:00.000
5952
-1
3001,2
-1
600
69985
139
2018-02-12 23:00:20.000
2018-02-11 00:00:00.000
5952
-1
3001,2
-1
600
70044
139
2018-02-13 02:00:09.000
2018-02-12 00:00:00.000
3350,4
-1
1525,8
-1
600
69404
149
2018-02-10 11:02:26.000
2018-02-09 00:00:00.000
1068,6
-1
369,8
-1
100
69463
149
2018-02-10 15:02:25.000
2018-02-09 00:00:00.000
1068,6
-1
369,8
-1
100
69531
149
2018-02-10 17:18:49.000
2018-02-09 00:00:00.000
1068,6
-1
369,8
-1
100
69699
149
2018-02-11 01:02:33.000
2018-02-10 00:00:00.000
992,8
-1
357,9
-1
100
69767
149
2018-02-11 17:20:01.000
2018-02-10 00:00:00.000
992,8
-1
357,9
-1
100
69768
149
2018-02-11 17:20:05.000
2018-02-10 00:00:00.000
992,8
-1
357,9
-1
100
69885
149
2018-02-12 17:20:10.000
2018-02-11 00:00:00.000
978,2
-1
380,5
-1
100
I need to calculate a power consumption for the Business units, a formula looks like this = (SummAPlus of CounterNumber 1) +(SummAPlus of CounterNumber 139) - (SummAPlus of CounterNumber 149)
It is very easy for each specific day, but I need automatic scaling for month, year and so on.
For this purpose I need to select only 1 row from the table for each DataDateT
For more convenient investigation, my pbix file with data is here.
Best regards, Dmitry
Hi dkushner，
From the details you provide, if I understand your requirement correctly, you could use Data Hierarchy to get the Business Units of Year, Month or Day. You could create the measure :
Business Units = var count1sum =CALCULATE(SUM(PowerCountersPreviousDay[SummAPlus]),FILTER(ALL(PowerCountersPreviousDay[CounterNumber]),PowerCountersPreviousDay[CounterNumber]=1)) var count139sum = CALCULATE(SUM(PowerCountersPreviousDay[SummAPlus]),FILTER(ALL(PowerCountersPreviousDay[CounterNumber]),PowerCountersPreviousDay[CounterNumber]=139) ) var count149sum = CALCULATE(SUM(PowerCountersPreviousDay[SummAPlus]),FILTER(ALL(PowerCountersPreviousDay[CounterNumber]),PowerCountersPreviousDay[CounterNumber]=149)) RETURN count139sum+count1sum-count149sum
You could refer to this picture below:
Reference for pbix file
Hope it can help you!
Best Regards,
Cherry
