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
