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 w...

Need Help

rubayatyasmin

Community Champion

3 years ago

hi, AlexXandyr

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
)