DAX Measure - Group based on TopN

DAX DAX Calculations
1

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!

image
image934Ă—712 38.5 KB

image
image997Ă—650 38.4 KB

2

Hi Ronald_Balza,

The problem is caused because you have a many to many relationship between the Custom group table (called “GroupedFinalisedInvoices”) and the Customer table. (Why, is not good for filtering data.)

The invoiced total, which is to be ranked, from table “FactFinalisedInvoicesDetailed” can clearly be connected to a customer, and also to a customer group which is given per customer in your Customer table.

But there is no clear (one to many) relationship to the Custom group table.
How to connect a single invoice amount to the Group Name from the Custom group table as there is no unambiguous relationship ?

If you want a top 10 from Group Owner from your Customer table, that’s no problem at all, use the standard Top10 DAX formula.

Customer

  • Code
  • Customer ID
  • Group Owner
  • Name

Custom group table (called “GroupedFinalisedInvoices”)

  • Group Code
  • Group Name
  • Group Owner
2 Likes
3

Hi @deltaselect, appreciated your time taking on this and well noted on your points. While it is true that the relationship is a mess, I figured out how to do the ranking using crossjoin function since it is on two separate table. I’ll take this a solution by the way :slight_smile: Cheers!