Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

TopN + Others in Matrix Totals

Hi,

I have a worked the solution explained here [https://www.proserveit.com/blog/ms-power-bi-topn-and-other], and I have the TopN and "Other" working correctly.

The problems comes when I need to show the same information in a Matrix.

1.- I can't sort the matrix in the same way the graph, the graph can sort by Rank but the matrix cannot.
2.- The Totals are not taking the "Other" quantity.. that's why I called Totals - OTHER, this is a problem as the user needs to see the TOTALS, including the Other not just the TopN



Here how I do the DAXs:

---------------------------

TOP_X = Parameter[Selected_TopN] //this comes from the slicer

---------------------------

SUBSCRIPTION_RANK =
IF(SELECTEDVALUE('SUBSCRIPTION_AND_OTHER'[PLAN_TYPE]) = "OTHER", [TOP_X] + 1, RANKX(ALL('SUBSCRIPTION_AND_OTHER'), [TOTAL_SUBSCRIPTION_VALUE]))
---------------------------
TOP_SUBSCRIPTION_AND_OTHER =
//If a category is ranked within the Top N, give us the value for that category.
if([SUBSCRIPTION_RANK] <= [TOP_X], [TOTAL_SUBSCRIPTION_VALUE],
//else we're outside the Top N, and we're looking at the "Other" category, aggregate the value for all categories outside the TopN
    if(SELECTEDVALUE('SUBSCRIPTION_AND_OTHER'[PLAN_TYPE]) = "OTHER", sumx(filter(all('SUBSCRIPTION_AND_OTHER'[PLAN_TYPE]), [SUBSCRIPTION_RANK] > [TOP_X]), [TOTAL_SUBSCRIPTION_VALUE])
)
)
---------------------------
TOTAL_SUBSCRIPTION_VALUE = CALCULATE(SUM(Table[TOT BY PLAN])

I really appreciate your help..
Need Help

1 Reply

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

    Could you provide some sample data so that I can investigate it further? Thanks.

     

    Best Regards,

    Community Support Team _ Jing Zhang