In summary I am trying to create a measure using the DAX that follows:
0. Variables to check filter context is a single person, used at the end.
1. (basetable), this is summary table of total sales by sales person and product (column: SalesPersonProduct) and a column of total sales for that product.
2. (SumTable), this table is attempting to summarise the basetable variable above by sales person across all of their products.
3. (ScoreTable) I calculate an avg score per sales person across all of their products out of 10
4. (RankTable) I then assign a rank based on the decile the sales person is part of with the above score.
I have two problems that I am looking to solve:
1.
When I produce a report with all of the sales people selected then the results are as expected.
When I filter the report to only show a selection of sales people (perhaps from specific country) then the ranks (across all sales people) are not maintained.
I have tried using the ALL function in the (basetable) filters but this seems to have no/little effect.
2.
Using the below DAX when I test the results of the SumTable table variable in a separate statement, for some sample people, it seems that something more complex is happening rather than the "simple" sum of SalesPersonProduct and SalesProduct that I want to achieve as the numbers are not always as expected.
Options considered:
- Data lineage, I suspect this could be part of the issue and that DAX is "remembering" the data lineage between variables and that's getting in the way. I am using SQL 2016 and so believe TREATAS solution is out of scope (or a driver to upgrade!)
- I have also tried re-writing the basetable variable using calcaulte / sum rather than SumX but still see the above problems.
Please note that I have changed table / column names to try and make the code more generic/readable and so please forgive any transpositions.
DAX
Measure =
VAR SingleSalesPerson =
COUNTROWS ( VALUES ( Person[Person ID] ) )
VAR Result =
IF ( SingleSalesPerson = 1, DISTINCT ( Person[Person ID] ), "" )
VAR basetable =
NATURALINNERJOIN (
FILTER (
SUMMARIZE (
FILTER (
Sales,
ISBLANK ( RELATED ( 'Sales Person'[Termination Date] ) )
&& RELATED ( 'Sales Person'[Employee Level] ) = "Director"
&& RELATED ( 'Sales Person'[Current Employee Level] ) = "Director"
),
'Sales Person'[Person ID],
'Product'[Product ID],
"SalesPersonProduct", SUM ( Sales[USD_BilledSalesValue] )
),
ROUND ( [SalesPersonProduct], 2 ) <> 0
),
FILTER (
SUMMARIZE (
FILTER ( Sales, RELATED ( 'Sales Period'[Calendar Year] ) = "2020" ),
'Product'[Product ID],
"SalesProduct", SUM ( Sales[USD_BilledFeeValue] )
),
ROUND ( [SalesProduct], 2 ) <> 0
)
)
var SumTable =
SUMMARIZE (
basetable,
'Sales Person'[Person ID],
"SalesPersonProduct",
VAR CurrentPerson =
DISTINCT ( 'Sales Person'[Person ID] )
RETURN
SUMX (
basetable,
IF ( 'Sales Person'[Person ID] = CurrentPerson, [SalesPersonProduct], 0 )
),
"SalesProduct",
VAR CurrentPerson =
DISTINCT ( 'Sales Person'[Person ID] )
RETURN
SUMX (
basetable,
IF ( 'Sales Person'[Person ID] = CurrentPerson, [SalesProduct], 0 )
)
)
VAR ScoreTable =
ADDCOLUMNS (
SumTable,
"Score",
IF (
ISBLANK ( MAX ( 0, MIN ( 10, (1-DIVIDE ( [Out], [TotalX] )) * 10 ) ) ),
0,
MAX ( 0, MIN ( 10, (1-DIVIDE ( [Out], [TotalX] )) * 10 ) )
)
)
VAR RankTable =
SUMMARIZE (
ScoreTable,
'Sales Person'[Person ID],
[Score],
"rRank",
VAR Dec1 =
PERCENTILEX.INC ( ScoreTable, [Score], 0.1 )
VAR Dec2 =
PERCENTILEX.INC ( ScoreTable, [Score], 0.2 )
VAR Dec3 =
PERCENTILEX.INC ( ScoreTable, [Score], 0.3 )
VAR Dec4 =
PERCENTILEX.INC ( ScoreTable, [Score], 0.4 )
VAR Dec5 =
PERCENTILEX.INC ( ScoreTable, [Score], 0.5 )
VAR Dec6 =
PERCENTILEX.INC ( ScoreTable, [Score], 0.6 )
VAR Dec7 =
PERCENTILEX.INC ( ScoreTable, [Score], 0.7 )
VAR Dec8 =
PERCENTILEX.INC ( ScoreTable, [Score], 0.8 )
VAR Dec9 =
PERCENTILEX.INC ( ScoreTable, [Score], 0.9 )
RETURN
IF (
[Score] <= Dec1,
1,
IF (
[Score] <= Dec2,
2,
IF (
[Score] <= Dec3,
3,
IF (
[Score] <= Dec4,
4,
IF (
[Score] <= Dec5,
5,
IF (
[Score] <= Dec6,
6,
IF (
[Score] <= Dec7,
7,
IF ( [Score] <= Dec8, 8, IF ( [Score] <= Dec9, 9, 10 ) )
)
)
)
)
)
)
)
)
Var rResult =
if(SingleSalesPerson<>1,BLANK(),
SUMX(Filter(RankTable,'Sales Person'[Person ID]=Result),[rRank])
)
Return
rResult
