Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I'm having some difficulty writing a formula to correctly display the figure I want on a KPI second measure. The measure should give a simple volume but stop at a certain level. There are 3 levels I'm working with; An over all view by default, with two filters which are[Manager] and [Name].
The KPI's first measure goes down to [Name] level as intended. The KPI's second measure should follow the same path up until [Name] level where it should instead give an average per person under [Manager].
An example; lets say the business has 1,000 sales. Manager A has a team of 10 people. That team accounts for 100 sales, each with varying personal sales. The KPI should show the following data.
[No Filters Selected] | First Measure = 1,000 | Second Measure = 1,000
[Manager Selected] | First Measure = 100 | Second Measure = 100
[Name Selected] | First Measure = 6 | Second Measure = 10
KPI First Measure
The first measure counts all unique sale ID's where the sale has been completed. With no filters selected this provides the overall view required. [Manager] and [Name] have their respective drill down affects.
COUNT({<[Sale Type] = {'Complete'}>}DISTINCT saleID)
KPI Second Measure
This is where I'm having the issue. Selecting [Manager] gives the average per person instead of total at [Manager] level first - skipping a step in what I want to occur. I understand that I'm missing a level in this code but I'm not sure how to go about adding it in.
IF(
ISNULL(
GetFieldSelections(Manager)),
COUNT({<[Sale Type] = {'Complete'}>}DISTINCT saleID)
,
COUNT({1<Manager = P(Manager),
[Sale Type] = {'Complete'}>}DISTINCT saleID)
/
COUNT({1<Manager = P(Manager),
[Sale Type] = {'Complete'}>}DISTINCT Name)
)
Many thanks
Perhaps this way
Sum(Aggr(IF(
ISNULL(
GetFieldSelections(Manager)),
COUNT({<[Sale Type] = {'Complete'}>}DISTINCT saleID)
,
COUNT({1<Manager = P(Manager),
[Sale Type] = {'Complete'}>}DISTINCT saleID)
/
COUNT({1<Manager = P(Manager),
[Sale Type] = {'Complete'}>}DISTINCT Name)
), Manager))
Unfortunately that didn't work. It threw off the number displayed on the second measure compared to the first when no filters are selected.
I'll use some figures from one of my examples make it easier. Using one of the [Manager] , there are 53 [Names]. The output I'm seeing with above alterations is:
[No Filters Selected] | First Measure = 6,056 | Second Measure = 6,317
[Manager Selected] | First Measure = 205 | Second Measure = 56
[Name] | First Measure = 9 | Second Measure = 3
The result I want to achieve is:
[No Filters Selected] | First Measure = 6,056 | Second Measure = 6,056
[Manager Selected] | First Measure = 205 | Second Measure = 205
[Name] | First Measure = 9 | Second Measure = 4
Many thanks