Forum Discussion

AUGUSF's avatar
AUGUSF
New Member
8 years ago

Cumulative Totals by Group plus manipulation

Hello,

 

I've been struggling with my data to get what I need, I could solve some of the problems, but couldn't put everything togheter:

 

I have 7 meters which record data every 15 minutes, I have an output like this:

 

TimeMeterValue
00:00A10
00:00B20
00:00C30
00:00D40
00:00E50
00:00F60
00:00G70
00:15A11
00:15B12
00:15C13
00:15D14
00:15E15
00:15F16
00:15G17

 

And so on, the readings are cumulative.

 

I managed get the daily (or weekly, etc) value from each meter using max and min functions and then filtering by meter directly in the report. The thing is, I need to do some math on the values so it would represent a real consumption, let's say:

 

C1 = A

C2 = B - C

C3 = C

C4 = D

C5 = E 

C6 = F - G - E - D

C7 = G

C8 = A - B - F

 

Using max()-min() it was easy to have the total amout read by the meter in a period of time, I filtered it directly in the Report using the Visuals. But I can't figure out how to calculate and plot C1-8 with that ease.

 

I tried this solution:

https://community.powerbi.com/t5/Desktop/Add-calculated-index-column-by-DAX/td-p/72448

 

using lookupvalue(value;index;index-1) but it returned an error about finding more than one value of index

 

I also tried making one different table for each meter and then creating Measures for the measured consumption in C1-8 over time, but I couldn't relate the measures to the timestamp to plot them afterwards.

 

It seems like I'm missing something, any kind of tips about how structure this data would be appreciated.

 

Regards

4 Replies

  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    8 years ago

    I would be helpful to either showed desired ouput or provide a sample file for someone to look at.  It's not clear what your current data model structure is or what desired output is.

    • AUGUSF's avatar
      AUGUSF
      New Member
      8 years ago

      Hello mattbrice, thanks for your reply.

      This is what my source looks like, they came from an ODBC server:

       

      "SourceID" is the meter code, which I first stated as A, B, C.. And "Value" the cumulative readings, the numbers seems weird because this particular meter (8406) is not calibrated yet, but assume it's a cumulative column over time.

       

      I want my output to be what was read by each meter in a given period of time, then I should add or subtract some of them togheter to get the mass balance right as in C1, C2, etc.

      I don't know if it's better/possible for my output (C1,C2...) to be a colum or a measure. I want the total daily value of Cn and plot it's value hourly.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi AUGUSF,

     

    Do you want a table like it below? If so, you can try it in this file. 

    Cumulative_Totals_by_Group_plus_manipulation

    1. A new table of new mapping pairs. (Simple in my test)

    NewMapping =
CROSSJOIN (
    VALUES ( Table1[Time] ),
    { "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8" }
)

    2. A bridge table.

    Time = VALUES(Table1[Time])

    3. Establish relationships.

    4. Use a measure like below. (these two have same functions.)

    Measure 2 =
VAR newRule =
    MIN ( 'NewMapping'[NewRule] )
RETURN
    SWITCH (
        newRule,
        "C1", CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "A" ),
        "C2", CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "B" )
            - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "C" ),
        "C3", CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "C" ),
        "C4", CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "D" ),
        "C5", CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "E" ),
        "C6", CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "F" )
            - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "G" )
            - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "E" )
            - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "D" ),
        "C7", CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "G" ),
        "C8", CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "A" )
            - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "B" )
            - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "F" )
    )
    Measure =
VAR newRule =
    MIN ( 'NewMapping'[NewRule] )
RETURN
    IF (
        newRule = "C1",
        CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "A" ),
        IF (
            newRule = "C2",
            CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "B" )
                - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "C" ),
            IF (
                newRule = "C3",
                CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "C" ),
                IF (
                    newRule = "C4",
                    CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "D" ),
                    IF (
                        newRule = "C5",
                        CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "E" ),
                        IF (
                            newRule = "C6",
                            CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "F" )
                                - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "G" )
                                - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "E" )
                                - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "D" ),
                            IF (
                                newRule = "C7",
                                CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "G" ),
                                IF (
                                    newRule = "C8",
                                    CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "A" )
                                        - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "B" )
                                        - CALCULATE ( SUM ( Table1[Value] ), 'Table1'[Meter] = "F" )
                                )
                            )
                        )
                    )
                )
            )
        )
    )

    Best Regards,

    Dale

    • AUGUSF's avatar
      AUGUSF
      New Member
      8 years ago

      Thanks for your answer Dale, I'll be trying this and reply the results afterwards.

      Regards.