Forum Discussion

kjsullivan's avatar
kjsullivan
Frequent Visitor
9 years ago
Solved

Top N with multiple variables

I am trying to make a 100% stacked bar chart that gives me the percentage of revenue shared by the top 2 industries in a state. I don't want the overall top 2 by all states. For example, Georgia could have industry x and y as there top 2 while Florida could have industry x and z. I saw that you could make a RANKX off of another post, but PowerBi kept giving me errors. The code is as follows: RANKX(ALL(Sheet1[Industry]),CALCULATE(SUM(Sheet1[Revenue]),ALLEXCEPT(Sheet1,Sheet1[Industry],Sheet1[State])),DESC,Dense). Does anyone know if there is a more efficient way or if my code is just wrong? Thanks.

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    kjsullivan,

     

    Sample data and sample DAX expression for you reference.
    Column = RANKX(FILTER(Table2,Table2[Country]=EARLIER(Table2[Country])),Table2[Amount],,DESC)

     

    If this is not what you want, please provide us some sample data and your expected result, so that we can make further analysis.

     

    Regards,

    Charlie Liao

2 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    9 years ago

    kjsullivan,

     

    Sample data and sample DAX expression for you reference.
    Column = RANKX(FILTER(Table2,Table2[Country]=EARLIER(Table2[Country])),Table2[Amount],,DESC)

     

    If this is not what you want, please provide us some sample data and your expected result, so that we can make further analysis.

     

    Regards,

    Charlie Liao