Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

topn dax for negative values

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 

Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    6 years ago

    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

     

     
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.