Forum Discussion
AUGUSF
8 years agoNew Member
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 ha...
v-jiascu-msft
Microsoft Employee
8 years agoHi AUGUSF,
Do you want a table like it below? If so, you can try it in this file.
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
8 years agoNew Member
Thanks for your answer Dale, I'll be trying this and reply the results afterwards.
Regards.