Forum Discussion

dkushner's avatar
dkushner
Frequent Visitor
9 years ago
Solved

Calculating Total by Measure

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

CounterNumberDateTSummAPlus
13928.03.2017 14:57:1115441850
15028.03.2017 14:57:2113468000
5128.03.2017 14:57:251252055
2328.03.2017 14:57:284623036
8328.03.2017 14:57:324888375
6728.03.2017 14:57:356716307
4428.03.2017 14:57:3913146
928.03.2017 14:57:42550344
13928.03.2017 14:58:0615441870
15028.03.2017 14:58:1013468010
5128.03.2017 14:58:131252056
2328.03.2017 14:58:174623044
8328.03.2017 14:58:204888375
6728.03.2017 14:58:246716308
4428.03.2017 14:58:2713146
928.03.2017 14:58:31550344
2328.03.2017 15:35:484623398
5128.03.2017 15:38:281252081

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's avatar
    Anonymous
    9 years ago
    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

     

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

     

    • dkushner's avatar
      dkushner
      Frequent Visitor
      9 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago
        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