Forum Discussion
Solved
Finding the difference for a long table
I am trying to create a measure to find the difference across dates between 2 different categories in the same Long Table... I tried sum(ValueA) - sum(ValueB), but it doesn't work
Sample Table:
| Date | Category | Value |
| 1/1/19 | A | 29 |
| 1/1/19 | B | 23 |
| 2/1/19 | A | 35 |
| 2/1/19 | B | 123 |
| 3/1/19 | A | 4 |
| 3/1/19 | B | 2123 |
| 4/1/19 | A | 445 |
| 4/1/19 | B | 32 |
So the measure should be able to have A - B for all dates, so for 1/1/19, it should give me 7 (29 - 23)
dragonus ,
measure = calculate(sum(Table[Value]), Table[Category]="A") - calculate(sum(Table[Value]), Table[Category]="B")
Hi dragonus ,
You could create a new column to get negative values.
Column = IF ( 'Table'[Category] = "A", 'Table'[Value], - 'Table'[Value] )Then create a measure:
Measure = CALCULATE ( SUM ( 'Table'[Column] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
2 Replies
dragonus ,
measure = calculate(sum(Table[Value]), Table[Category]="A") - calculate(sum(Table[Value]), Table[Category]="B")
Hi dragonus ,
You could create a new column to get negative values.
Column = IF ( 'Table'[Category] = "A", 'Table'[Value], - 'Table'[Value] )Then create a measure:
Measure = CALCULATE ( SUM ( 'Table'[Column] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )