Forum Discussion

rbalza's avatar
rbalza
Icon for Helper III rankHelper III
5 years ago
Solved

Group based on TopN

Hi PowerBi Superstars,   Would like to know, how will I group this data based on TopN? I have a custom group table and I wanted to use it as a basis of TopN. Having a hard time to pull this off. Sa...
DAX help needed
Need Help DAX
Need Help DAX measure
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, rbalza 

    I am not quite sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below.

    All measures (two measures) are in the sample pbix file.

     

    Jihwan Rank by Invoice Type =
    RANKX(ALLSELECTED(FactFinalisedInvoicesDetailed[Invoice Type]),[Total Invoice], , DESC)
     
    Jihwan Invoice Total by Group =
    SUMX (
    KEEPFILTERS (
    FILTER (
    ALLSELECTED ( FactFinalisedInvoicesDetailed[Invoice Type] ),
    COUNTROWS (
    FILTER (
    'Customer Rank',
    [Jihwan Rank by Invoice Type] >= 'Customer Rank'[Min Rank]
    && [Jihwan Rank by Invoice Type] <= 'Customer Rank'[Max Rank]
    )
    ) > 0
    )
    ),
    [Total Invoice]
    )
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

     

     

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, rbalza 

    Thank you for your feedback.

    If you can see my measure, the ranking is only considering Invoice Type.

    If you change the ranking measure to consider Invoice Type and Group Owner together, it will work.

     

    Thank you.

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, rbalza 

    Thank you for your feedback.

    Please check the below if it is what you are looking for.

    The measures are amended.

     

    https://www.dropbox.com/s/170gm4rf0r1u8sh/sampledata%20%281%29%20%281%29.pbix?dl=0 

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, rbalza 

I am not quite sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below.

All measures (two measures) are in the sample pbix file.

 

Jihwan Rank by Invoice Type =
RANKX(ALLSELECTED(FactFinalisedInvoicesDetailed[Invoice Type]),[Total Invoice], , DESC)
 
Jihwan Invoice Total by Group =
SUMX (
KEEPFILTERS (
FILTER (
ALLSELECTED ( FactFinalisedInvoicesDetailed[Invoice Type] ),
COUNTROWS (
FILTER (
'Customer Rank',
[Jihwan Rank by Invoice Type] >= 'Customer Rank'[Min Rank]
&& [Jihwan Rank by Invoice Type] <= 'Customer Rank'[Max Rank]
)
) > 0
)
),
[Total Invoice]
)
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 

 

  • rbalza's avatar
    rbalza
    Icon for Helper III rankHelper III
    5 years ago

    Hi Jihwan_Kim , thanks for your time solving this. It is getting there, however,  when I place the group owner, it doesn't display properly the topN anymore.

     

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      Hi, rbalza 

      Thank you for your feedback.

      If you can see my measure, the ranking is only considering Invoice Type.

      If you change the ranking measure to consider Invoice Type and Group Owner together, it will work.

       

      Thank you.

       

      Hi, My name is Jihwan Kim.

       

      If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

       

      Linkedin: linkedin.com/in/jihwankim1975/

      Twitter: twitter.com/Jihwan_JHKIM

      • rbalza's avatar
        rbalza
        Icon for Helper III rankHelper III
        5 years ago

        Jihwan_Kim that is actually my problem. As you can see on my sample data, I had both invoice type and group owner, which still doesn't work. Any tips?