Summarizing using table variables

Anonymous — Tue, 15 Sep 2020 18:44:25 GMT

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

Re: Summarizing using table variables

Anonymous — Wed, 16 Sep 2020 20:38:14 GMT

Appreciate my original post covers quite a bit of ground and so any tips on or proforma code that helps show how to summarise two different metrics and then re-summarise at a different grain would be appreciated.

topic Summarizing using table variables in DAX Commands and Tips

Summarizing using table variables

Re: Summarizing using table variables