Calculating Total by Measure

[dkushner]

Hello,

Could anybody help me with a Calculating Total by Measure? I don’t understand how to do it.

I am working by direct query with MS SQL database.

I have a table, in the one column I have the counters in the second  figures of the power consumption

CounterNumber	DateT	SummAPlus
139	28.03.2017 14:57:11	15441850
150	28.03.2017 14:57:21	13468000
51	28.03.2017 14:57:25	1252055
23	28.03.2017 14:57:28	4623036
83	28.03.2017 14:57:32	4888375
67	28.03.2017 14:57:35	6716307
44	28.03.2017 14:57:39	13146
9	28.03.2017 14:57:42	550344
139	28.03.2017 14:58:06	15441870
150	28.03.2017 14:58:10	13468010
51	28.03.2017 14:58:13	1252056
23	28.03.2017 14:58:17	4623044
83	28.03.2017 14:58:20	4888375
67	28.03.2017 14:58:24	6716308
44	28.03.2017 14:58:27	13146
9	28.03.2017 14:58:31	550344
23	28.03.2017 15:35:48	4623398
51	28.03.2017 15:38:28	1252081

At the first step I need to find the maximum & the minimum figures for the each counter, then calculate the difference between MAX and MIN. It was very easy:

Delta by Aplus = MAX(PowerCounters[SummAPlus]) - MIN(PowerCounters[SummAPlus])

And now I need to calculate the Total(SUM) of "Delta by Aplus", I don't understand how to do it.

I tried to create a Card, but I had got there only static figure not affected by Slicers

If I just switch on the Total, I don't understand the result at all, the Total of (Delta between MAX and MIN values) is almost equal of the Total of MAX values.

[Anonymous]

Total Delta := SUMX(VALUES(PowerCounters[CounterNumber]), [Delta by Aplus])

[dkushner]

Thanks, it’s working. now one more question.

If I need to implement 1  more filter by measure (I need to select exact Counters from the column). What should I do?

Or how can I use a filter directly in the formula. For example I would like to calculate Total Delta by Aplus only for 2 counters with the numbers 18 and 50.

[v-yuezhe-msft · Accepted solution]

---

@dkushner wrote:

Thanks, it’s working. now one more question.

If I need to implement 1  more filter by measure (I need to select exact Counters from the column). What should I do?

Or how can I use a filter directly in the formula. For example I would like to calculate Total Delta by Aplus only for 2 counters with the numbers 18 and 50.

---

Hi @dkushner,

Please check that if the following DAX returns your expected result.

sumdelta = SUMX(VALUES(PowerCounters[CounterNumber]), [Delta by Aplus])
Total Delta = CALCULATE([sumdelta],FILTER(PowerCounters,PowerCounters[CounterNumber]= "18"||PowerCounters[CounterNumber]= "50"))

However, if you want to calculate SUM of “Delta by Aplus” in a specific date range, create a date column using following DAX.

Date = DATE(YEAR(PowerCounters[DateT]),MONTH(PowerCounters[DateT]),DAY(PowerCounters[DateT]))

Then create the following measures, specify date range in the filter of Total Delta measure.

sumdelta = SUMX(VALUES(PowerCounters[CounterNumber]), [Delta by Aplus])
Total Delta = CALCULATE([sumdelta],FILTER(PowerCounters,PowerCounters[Date]<=DATE(2017,3,29) && PowerCounters[Date]>=DATE(2017,3,28)))

Thanks,
Lydia Zhang

[Anonymous]

Any filtering you add to the report/page/visual should in theory... "just work".   Is that not the case?

[dkushner]

It seems to me I don't explain the task correctly ))

Your formula “Total Delta := SUMX(VALUES(PowerCounters[CounterNumber]), [Delta by Aplus])”

Is calculating SUM of “Delta by Aplus” for all unique Counters in the Column, and now if I won’t to calculate  SUM of “Delta by Aplus” not for all counters, I have to create a special column with already filtered date.

And I am wondering how to do it using DAX formulas )