topn dax for negative values

[Anonymous]

hi,

i have no problem getting result for topn dax for positive values. however, i need help to resolve the topn dax for negative values.

this is my typical topn dax:

TopN.ADPR =
CALCULATE (
[16000-ADPR.EM],
TOPN (
'mn-value'[n-value Value],
ALL ( 'mprofile'[Org_Name] ),
[16000-ADPR.EM]
),
VALUES ( 'mprofile'[Org_Name] )
)

i'd appreciate any help in this.

tks, -nik 

[amitchandak · Accepted solution]

@Anonymous 

Sort Ascending should give you top negative numbers. Or Top means all near to 0. Then have a filter of <0 in your measure

order
(Optional) A value that specifies how to sort orderBy_expression values, ascending or descending:

value	alternate value	Description
0 (zero)	FALSE	Sorts in descending order of values of order_by. This is the default value when order parameter is omitted.
1	TRUE	Ranks in ascending order of order_by.

Or use rank

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[amitchandak · Accepted solution]

@Anonymous 

Sort Ascending should give you top negative numbers. Or Top means all near to 0. Then have a filter of <0 in your measure

order
(Optional) A value that specifies how to sort orderBy_expression values, ascending or descending:

value	alternate value	Description
0 (zero)	FALSE	Sorts in descending order of values of order_by. This is the default value when order parameter is omitted.
1	TRUE	Ranks in ascending order of order_by.

Or use rank

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[Anonymous]

thanks for your time & efforts in trying to help, @amitchandak.

if top means all near to 0, please advise on how to have a filter of <0 in my measure in same dax that wrote earlier?

krgds, -nik

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a What-if parameter table as below.

mn-value = GENERATESERIES(0, 6, 1)

Then you need to create a measure like below.

Result = 
var _topn = SELECTEDVALUE('mn-value'[mn-value])
return
CALCULATE(
    SUM('Table'[Value]),
    TOPN(
        _topn,
        FILTER(
            ALL('Table'),
            'Table'[Value]<0
        ),
        'Table'[Value]
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

thanks, @v-alq-msft.

i'm sorry for not responding earlier (took a short break). also, i'm sorry for not making it clearer. 

the situation is if i have all negative values, how do i do top-n ranking for them?

Using the table you reproduced, the list will be as follows:

Name	Value
Amy	-11
Allen	-20
Mike	-19
Micheal	-46
Lucy	-16
Lily	-33
Jack	-25
John	-10

if i filter for top-3, it'll be-

Name	Value
John	-10
Amy	-11
Lucy	-16

how will the dax b to rank the negative values as in the case above, please?

tks & kind rgds, -nik

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may create a what-if parameter as below.

Then you may create a measure as below.

IsDisplay = 
var _topn = SELECTEDVALUE(Parameter[Parameter])
var _name = SELECTEDVALUE('Table'[Name])
var tab = 
ADDCOLUMNS(
    ALLSELECTED('Table'),
    "Rank",
    RANKX(
        ALLSELECTED('Table'),
        'Table'[Value]
    )
)
var _rank = 
SUMX(
    FILTER(
        tab,
        [Name] = _name
    ),
    [Rank]
)
return
IF(
    _rank<=_topn,
    1,0
)

Finally you need to put the measure in the visual level filter of a table visual. Here is the result.

Best Regards

Allan

[Anonymous]

many thanks, @v-alq-msft .

i shall try those suggestions.

krgds, -nik