Forum Discussion

AlexXandyr's avatar
AlexXandyr
Icon for Helper I rankHelper I
3 years ago

Calculating Top 1-30 with filter

Hello,
Im trying to do a top n (1-30) but excluding one set of data (other). But now when adding the filter/exclusion i filter that away but I see not only top 1-30 but all data. Any idea what is wrong, and why it shows all data and not only top 1-30?

Top1to30AmountTEST =
VAR Top30 =
TOPN(
    30,
    FILTER(
        VALUES('Income_2018->'[PartyGrouping]) ,
        'Income_2018->'[PartyGrouping] <> "Other"
        ) ,
        CALCULATE( SUM('Income_2018->'[Local Amount (correct sign)]) ,
        ALLSELECTED('Income_2018->'[PartyGrouping])))
             
VAR Top0 =
TOPN( -1,
    FILTER(
        VALUES('Income_2018->'[PartyGrouping]) ,
        'Income_2018->'[PartyGrouping] <> "Other"
        ) ,
        CALCULATE( SUM('Income_2018->'[Local Amount (correct sign)]) ,
        ALLSELECTED('Income_2018->'[PartyGrouping])))
VAR Top1to30 =
EXCEPT(Top30, Top0 )
return
CALCULATE('Income_2018->'[Income] , Top1to30, VALUES('Income_2018->'[PartyGrouping]
)
)
Need Help

4 Replies

  • AlexXandyr's avatar
    AlexXandyr
    Icon for Helper I rankHelper I
    3 years ago

    Now the information looks like this, where my test measure excludes Other but give me more data than just top 1-30

     

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    hi, AlexXandyr 

     

    1. The Top1to30 variable aims to subtract everything from Top0 from Top30. But given the logic, Top1to30 would be an empty set.

    The problem arises with this approach. When you try to get Top30 and then get Top0 (which is essentially all data minus "Other"), subtracting Top0 from Top30 doesn't give you the top 1 to 30 results.

    Instead, you can simplify the logic by directly filtering the top 30 results excluding the "Other" category.

     

    for example,

     

    Top1to30AmountTEST =
    VAR Top1to30 =
    CALCULATETABLE(
    TOPN(
    30,
    VALUES('Income_2018->'[PartyGrouping]),
    CALCULATE( SUM('Income_2018->'[Local Amount (correct sign)]),
    'Income_2018->'[PartyGrouping] <> "Other" )
    ),
    'Income_2018->'[PartyGrouping] <> "Other"
    )

    RETURN
    CALCULATE(
    'Income_2018->'[Income],
    Top1to30
    )

     

    • AlexXandyr's avatar
      AlexXandyr
      Icon for Helper I rankHelper I
      3 years ago

      Hello,
      Thank you for the reply. However this seems not to work on my data. It doesnt filter top 30 and only gives me same value for all customers.