topic Re: RT & Break Tie issue - not working for some rows, or at correct granularity in DAX Commands and Tips

RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 13:23:07 GMT

See below

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 09:13:28 GMT

Getting weird results for some rows in March, and all rows in the Grand Total.

See picture here:

Please help,

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 09:21:09 GMT

File with calcs:

https://1drv.ms/x/s!Ahu8OSSAtOMzlSKpKX7c8kRQySQT?e=ABTegh

What I tried:

I tried the following pattern, but it did not work:

Cumulative[GBP]:=VAR PersonRank = [Person Rank]

RETURN

CALCULATE (

[Amount[GBP]]],

TOPN (

[Person Rank],

GROUPBY (

FILTER( ALL ( Payroll_Table ), [Person Rank] <= PersonRank),

[User ID],

[First Name],

[Last Name]

CALCULATE ( SUM ( [Amount] ), ALL(Payroll_Table[P&L], Payroll_Table[Department], Payroll_Table[Job Grade]))

)

I also then tried this:

Cumulative by Person =

var __currentPersonRank = [Person Rank]

var __personsWithRankNotAbove =

FILTER(

ALLSELECTED( Payroll_Table[User ID] ),

[Person Rank] <= __currentPersonRank

)

var __totalUpToCurrentPerson =

CALCULATE(

[Total Amount],

__personsWithRankNotAbove

)

return

__totalUpToCurrentPerson

This worked for the User Subtotal lines, but not the N.L lines

In the Running Total Column:

Results, for the 'Salary Line' should be:

29,750 for person 1

51,833 for person 2

71,833 for person 3

91,875 for person 4

The summing order will be different for the 'Pension' line because the ranking is different, across the 4 people.

Jan, and Feb, return the correct results, just not some rows in March, and all rows in the Grand Total column.

What you see in the picture above is this:

Cumulative[GBP]:=VAR PersonRank = [Person Rank]

RETURN

CALCULATE (

[Amount[GBP]]],

TOPN (

[Person Rank],

GROUPBY (

FILTER( ALL ( Payroll_Table ), [Person Rank] <= PersonRank),

[User ID],

[First Name],

[Last Name], [P&L], [Department], [Job Grade]

CALCULATE ( SUM ( [Amount] ))

)

My [Person Rank Measure] (which works correctly) is this:

Person Rank:=SWITCH (

TRUE (),

NOT ( ISFILTERED ( [User ID] ) ),

COUNTROWS (

GROUPBY ( ALL ( Payroll_Table ), [User ID], [First Name], [Last Name] )

RANKX (

GROUPBY ( ALL ( Payroll_Table ), [User ID], [First Name], [Last Name] ),

CALCULATE (

SUM ( [Amount] ),

ALL ( Payroll_Table[P&L], Payroll_Table[Department], Payroll_Table[Job Grade] )

DESC,

SKIP

)

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 11:08:07 GMT

This is now resolved:

Final Measures used

Cumulative[GBP] :=
VAR PersonRank = [Person Rank]
RETURN
CALCULATE (
[Amount [GBP]] by Person],
TOPN (
[Person Rank],
GROUPBY ( ALL ( Payroll_Table ), [User ID], [First Name], [Last Name] ),
CALCULATE (
SUM ( [Amount] ),
ALL ( Payroll_Table[P&L], Payroll_Table[Department], Payroll_Table[Job Grade] )
)
)
)

Amount [GBP] by Person:=CALCULATE (
SUM ( [Amount] ),
ALL ( Payroll_Table[P&L], Payroll_Table[Department], Payroll_Table[Job Grade] )
)

I'm now just working out how to break tied ranks, in case for example, two people have the same salary in the same month.

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 11:02:11 GMT

I would like to get to a better Rank Tie Measure

If you see in the pic below:

Rank 1, works fine, except for tied ranks, it returns duplicates (see peach coloured cells in this column)

Rank 2, the newer measure works better, but only returns the correct results at a 'User ID' level (see green coloured cells)

How do I get Rank 2 to return similar results on the 'NL lines'

Picture

Measure so far

Person Rank with Break Ties:=VAR CurrentPersonTotal = [Amount [GBP]]]

VAR CurrentPerson =

MAX ( [User ID] )

VAR AllRelevantPersons =

FILTER (

ALLSELECTED ( Payroll_Table[User ID] ),

[Amount [GBP]]] >= CurrentPersonTotal

VAR TotalAmount = [Amount [GBP]]]

VAR IteratedPerson = [User ID]

RETURN

( TotalAmount > CurrentPersonTotal ) + ( TotalAmount = CurrentPersonTotal ) * ( IteratedPerson < CurrentPerson )

)

VAR Result =

1 + COUNTROWS ( AllRelevantPersons )

VAR ShouldCalc =

ISFILTERED ( [User ID] ) && HASONEVALUE ( Payroll_Table[User ID] )

RETURN

IF ( ShouldCalc, Result )

Link to file

https://drive.google.com/file/d/1WsPjnSBXcYrmd1rDmXSQqQ1pcSP1hYe1/view?usp=sharing

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 13:14:07 GMT

I think this is the only issue I have left now, with this:

Can't get the ranking to work on the 'NL' lines in the Rank 2 column.... : (

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 14:08:19 GMT

Now fixed,

It was all to do with this line in bold:

Person Rank with Break Ties :=
VAR CurrentPersonTotal = [Amount [GBP]] by Person]
VAR CurrentPerson =
    MAX ( [User ID] )
VAR AllRelevantPersons =
    FILTER (
        FILTER (
            GROUPBY ( ALL ( Payroll_Table ), [User ID], [First Name], [Last Name] ),
            [Amount [GBP]] by Person] >= CurrentPersonTotal
        ),
        VAR TotalAmount = [Amount [GBP]] by Person]
        VAR IteratedPerson = [User ID]
        RETURN
             ( TotalAmount > CurrentPersonTotal ) + ( TotalAmount = CurrentPersonTotal ) * ( IteratedPerson < CurrentPerson )
    )
VAR Result =
    1 + COUNTROWS ( AllRelevantPersons )
VAR ShouldCalc =
    ISFILTERED ( [User ID] ) && HASONEVALUE ( Payroll_Table[User ID] )
RETURN
    IF ( ShouldCalc, Result )

(Rank 2 column now has now breaks any ties and also works on 'NL lines')

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 15:04:33 GMT

I spoke too soon:

Now, I'm getting very strange behaviour

Person Rank with Ties :=
SWITCH (
    TRUE (),
    NOT ( ISFILTERED ( [User ID] ) ),
        COUNTROWS (
            GROUPBY ( ALL ( Payroll_Table ), [User ID], [First Name], [Last Name] )
        ),
    RANKX (
        GROUPBY ( ALL ( Payroll_Table ), [User ID], [First Name], [Last Name] ),
        [Amount [GBP]] by Person],
        ,
        DESC,
        SKIP
    )
)

Person Rank without Ties :=
VAR CurrentPersonTotal = [Amount [GBP]] by Person]
VAR CurrentPerson =
    MAX ( [User ID] )
VAR AllRelevantPersons =
    FILTER (
        FILTER (
            GROUPBY ( ALL ( Payroll_Table ), [User ID], [First Name], [Last Name] ),
            [Amount [GBP]] by Person] >= CurrentPersonTotal
        ),
        VAR TotalAmount = [Amount [GBP]] by Person]
        VAR IteratedPerson = [User ID]
        RETURN
             ( TotalAmount > CurrentPersonTotal ) + ( TotalAmount = CurrentPersonTotal ) * ( IteratedPerson < CurrentPerson )
    )
VAR Result =
    1 + COUNTROWS ( AllRelevantPersons )
VAR ShouldCalc =
    ISFILTERED ( [User ID] ) && HASONEVALUE ( Payroll_Table[User ID] )
RETURN
    IF ( ShouldCalc, Result )

Cumulative (with Ties) :=
VAR PersonRank = [Person Rank with Ties]
RETURN
    CALCULATE (
        [Amount [GBP]] by Person],
        TOPN (
            [Person Rank with Ties],
            GROUPBY ( ALL ( Payroll_Table ), [User ID], [First Name], [Last Name] ),
            [Amount [GBP]] by Person]
        )
    )

Cumulative (without Ties) :=
VAR PersonRank = [Person Rank without Ties]
VAR PersonWithRankNotAbove =
    FILTER (
        GROUPBY ( ALL ( Payroll_Table ), [User ID], [First Name], [Last Name] ),
        [Person Rank without Ties] <= PersonRank
    )
VAR TotalUptoCurrentPerson =
    CALCULATE ( [Amount [GBP]] by Person], PersonWithRankNotAbove )
RETURN
    TotalUptoCurrentPerson

The last measure, gives this, works at a User ID level but doesn't work for the 'NL' lines - they return just the same total for each user:

But, when within the last measure, Cumulative (without Ties), I use the Person Rank with Ties measure, instead of the Person Rank without Ties meaure, it returns this, working at both User ID level, and NL lines level but obviously doesn't break any ties

Can anyone help?

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 16:25:12 GMT

It was a Table structure thing, so I had to further modify the measure:

- Forgot the lines in bold

- Also, line in red, can be as it is, or just ALL (Payroll_Table), - it makes no difference in this case.

Cumulative (without Ties) :=
VAR PersonRank = [Person Rank without Ties]
VAR PersonWithRankNotAbove =
    FILTER (
        GROUPBY (
            ALLEXCEPT ( Payroll_Table, Payroll_Table[Date], Payroll_Table[NL Line] ),
            [User ID],
            [First Name],
            [Last Name],
            [P&L],
            [Department],
            [Job Grade]
        ),
        [Person Rank without Ties] <= PersonRank
    )
VAR TotalUptoCurrentPerson =
    CALCULATE ( [Amount [GBP]] by Person], PersonWithRankNotAbove )
RETURN
    TotalUptoCurrentPerson

Now, getting broken ties (i.e no tied rnakes) across all lines, like I wanted! This makes the Cumulative %, more accurate in a way, as it prevents it from jumping up, for any tied ranks. : )

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Fri, 02 Oct 2020 09:32:16 GMT

Here is what I ended up doing:

In addition to the change in the post above, I replaced ALL (Payroll Table), in a few measures with ALLSELECTED (Column 1, Column 2, etc.). As ALLSELECTED doesn't support multiple columns yet in Excel / PowerPivot, I had to wrap this in a SUMMARIZE. Using ALLSELECTED was better for me, as I wanted the Ranking, and Cumulative columns to also be responsive to the selected Context of Columns in the Pivot Table. This was not possible by using just ALL (Payroll Table), or even by just using ALLEXCEPT( Payroll Table, Date, NL Line)....

Amount[GBP] :=
CALCULATE ( SUM ( [Amount] ) )

Amount [GBP] by Person :=
CALCULATE (
    SUM ( [Amount] ),
    ALLSELECTED ( Payroll_Table[P&L] ),
    ALLSELECTED ( Payroll_Table[Department] ),
    ALLSELECTED ( Payroll_Table[Job Grade] )
)

Total Amount [GBP] Selected Persons Only :=
CALCULATE (
    SUM ( [Amount] ),
    ALLSELECTED ( Payroll_Table[User ID] ),
    ALLSELECTED ( Payroll_Table[First Name] ),
    ALLSELECTED ( Payroll_Table[Last Name] ),
    ALL ( Payroll_Table[P&L] ),
    ALL ( Payroll_Table[Department] ),
    ALL ( Payroll_Table[Job Grade] )
)

Amount as [%] of Total :=
DIVIDE (
    [Amount [GBP]] by Person],
    [Total Amount [GBP]] Selected Persons Only],
    BLANK ()
)

Person Rank with Ties :=
IF (
    NOT ( HASONEVALUE ( Payroll_Table[User ID] ) ),
    COUNTROWS ( GROUPBY ( ALLSELECTED ( Payroll_Table ), [User ID] ) ),
    RANKX (
        GROUPBY (
            SUMMARIZE (
                ALLSELECTED ( Payroll_Table ),
                [User ID],
                [First Name],
                [Last Name],
                [P&L],
                [Department],
                [Job Grade]
            ),
            [User ID],
            [First Name],
            [Last Name]
        ),
        [Amount [GBP]] by Person],
        ,
        DESC,
        SKIP
    )
)

Person Rank without Ties :=
VAR CurrentPersonTotal = [Amount [GBP]] by Person]
VAR CurrentPerson =
    MAX ( [User ID] )
VAR AllRelevantPersons =
    FILTER (
        FILTER (
            GROUPBY (
                SUMMARIZE (
                    ALLSELECTED ( Payroll_Table ),
                    [User ID],
                    [First Name],
                    [Last Name],
                    [P&L],
                    [Department],
                    [Job Grade]
                ),
                [User ID],
                [First Name],
                [Last Name]
            ),
            [Amount [GBP]] by Person] >= CurrentPersonTotal
        ),
        VAR TotalAmount = [Amount [GBP]] by Person]
        VAR IteratedPerson = [User ID]
        RETURN
             ( TotalAmount > CurrentPersonTotal ) + ( TotalAmount = CurrentPersonTotal ) * ( IteratedPerson < CurrentPerson )
    )
VAR Result =
    1 + COUNTROWS ( AllRelevantPersons )
VAR ShouldCalc =
    ISFILTERED ( [User ID] ) && HASONEVALUE ( Payroll_Table[User ID] )
RETURN
    SWITCH (
        TRUE (),
        NOT ( HASONEVALUE ( Payroll_Table[User ID] ) ), COUNTROWS ( GROUPBY ( ALLSELECTED ( Payroll_Table ), [User ID] ) ),
        ShouldCalc, Result,
        BLANK ()
    )

Re: RT & Break Tie issue - not working for some rows, or at correct granularity

Anonymous — Thu, 01 Oct 2020 17:59:04 GMT

ctd...

Cumulative [GBP] (without Ties) :=
VAR PersonRank = [Person Rank without Ties]
VAR PersonWithRankNotAbove =
    FILTER (
        GROUPBY (
            SUMMARIZE (
                ALLSELECTED ( Payroll_Table ),
                [User ID],
                [First Name],
                [Last Name],
                [P&L],
                [Department],
                [Job Grade]
            ),
            [User ID],
            [First Name],
            [Last Name],
            [P&L],
            [Department],
            [Job Grade]
        ),
        [Person Rank without Ties] <= PersonRank
    )
VAR TotalUptoCurrentPerson =
    CALCULATE ( [Amount [GBP]] by Person], PersonWithRankNotAbove )
RETURN
    TotalUptoCurrentPerson

Cumulative (with Ties) :=
VAR PersonRank = [Person Rank with Ties]
RETURN
    CALCULATE (
        [Amount [GBP]] by Person],
        TOPN (
            PersonRank,
            GROUPBY (
                SUMMARIZE (
                    ALLSELECTED ( Payroll_Table ),
                    [User ID],
                    [First Name],
                    [Last Name],
                    [P&L],
                    [Department],
                    [Job Grade]
                ),
                [User ID],
                [First Name],
                [Last Name]
            ),
            [Amount [GBP]] by Person]
        )
    )

Cumulative [%] :=
DIVIDE (
    [Cumulative [GBP]] (without Ties)],
    [Total Amount [GBP]] Selected Persons Only],
    BLANK ()
)

Works pretty well for me so far! : )