Group based on TopN

[rbalza]

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. Sample data is attached. Thanks!

https://www.dropbox.com/s/xszrjtxt9oo8k76/sampledata.pbix?dl=0

[Jihwan_Kim · Accepted solution]

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]

)

https://www.dropbox.com/s/2udrs6a0kz66q40/sampledata%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 · Accepted solution]

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 · Accepted solution]

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]

)

https://www.dropbox.com/s/2udrs6a0kz66q40/sampledata%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

[rbalza]

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 · Accepted solution]

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]

@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?

[Jihwan_Kim · Accepted solution]

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

[rbalza]

Hi @Jihwan_Kim, as you can see on your total doesn't fit on the topN anymore. Like top 10 should be the highest amount right? Anyway, I figured out what the problem is. Since it is on a separate table, I used crossjoin function to rank both invoice and group owner. HOWEVER, the issue now is, there were same company that is not summing up together. Really appreciate your taking time on this.

[amitchandak]

@rbalza , You want TOPN to be one group and the other to another group?

The expected output is not very clear to me.

IS something similar to these

TOp N with Others
https://www.youtube.com/watch?v=UAnylK9bm1I
https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

[rbalza]

@amitchandak TopN based on the group below. and It shouldn't be on a slicer. It's like the topn will be above on datas (like a drop down thing)