Rankx issue (I checked a lot of the others already)

[RMDNA]

Hey all, I already checked a lot of the solved "rankx" questions, but I didn't see anything that dealt with something as potentially simple as mine. Hopefully that'll make it easy to fix.

In a [Sales] table, I have [product units sold] out of [total sold], giving me a [% of total], which all looks correct. However, my rankx:

Sales Units Rank = rankx(all(Sales), Sales[% Sales Units])

gives weird values sometimes (see picture). In the end, my running total is:

Sales Unit Running Total = 
CALCULATE (
    SUM ( Sales[% Sales Units] ),
    ALL ( Sales ),
    Sales[Sales Units Rank] <= EARLIER (Sales[Sales Units Rank] )
)

Any tips?

[v-yuta-msft]

Hi RMDNA,

Based on your description, there’re three original columns [Sales Units], [Sales Units Total] and [% Sales Units] in your table, right?

To calculate running total which can meet your requirement, you can modify DAX formula like below:

Sales Unit Running Total =

CALCULATE (

    SUM ( Sales[% Sales Units] ),

    ALL ( Sales ),

    Sales[Sales Units Rank] <= EARLIER (Sales[Sales Units Rank] ),

    Sales[Sales Units] = EARLIER(Sales[Sales Units Rank]),

    Sales[Sales Unit Total] =  EARLIER(Sales[Sales Unit Total])

)

Regards,

Jimmy Tao

[RMDNA]

@v-yuta-msft,

'Sales Units' comes directly from the table, while 'Sales Unit Total' and '% Sales Units' are either calculated columns or measures (I've tried both).

When I try any of the running total examples, I get the following:

"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

[Zubair_Muhammad]

@RMDNA

Try converting these to MEASURE...perhaps

Sales Units Rank =
RANKX ( ALLSELECTED ( Sales ), CALCULATE ( SUM ( Sales[% Sales Units] ) ) )

Sales Unit Running Total =
VAR myrank = [Sales Units Rank]
RETURN
    CALCULATE (
        SUM ( Sales[% Sales Units] ),
        ALLSELECTED ( Sales ),
        [Sales Units Rank] <= myrank
    )

[RMDNA]

@Zubair_Muhammad,

Using your rank code as a measure [SUR] gives the following, which will still throw off the running total. It looks better, but something is still off.