I might be bad at googling as I can't find an answer to this seemingly simple puzzle.
I have the following table in a tabular cube (2013)
**ProjectId Department Region Amount**
100 Dep1 Reg1 300
102 Dep1 Reg1 -300
103 Dep1 Reg1 -100
104 Dep2 Reg1 300
154 Dep2 Reg1 400
187 Dep2 Reg1 -200
198 Dep3 Reg1 -100
199 Dep3 Reg1 300
The first three attributes are part of an organisational hierarchy. I want to create a measure that counts the number of departments with an aggregated [Amount] less than 0. In this case, 'Dep1' is -100 and should be counted as '1' when looking at the parent region (Reg1).
Any ideas on how this measure can be written - so that it can be analysed using the organisational hierarchy in this table? All help is much appreciated!
I have tried the following:
This measure only yielded the total number of departments - but could not be analysed based on the organisational hierarchy from the main table used in the example.
I think that you need a measure like this:
Negative Department Count =
CALCULATE (
DISTINCTCOUNT ( Table[Department] ),
FILTER ( Table, [Amount] < 0 )
)
It will count each distinct department with Amount less than zero.