Forum Discussion

NonnaBlue's avatar
NonnaBlue
Frequent Visitor
4 years ago
Solved

Sum Multiple Top N Entires

I have a matrix with month's as rows and various values for each month.  One of those values is Tons.

I have created a measure called "Largest Customer" - shown below - that gets the TopN of Tons and shows the Customer Name & Tons.

Largest Customer = CONCATENATEX(TOPN(1, 'BBS_Backlog', BBS_Backlog[Tons]), BBS_Backlog[Sold To Customer] & " | " & Format(BBS_Backlog[Tons], "0.0"), UNICHAR(10), BBS_Backlog[Tons], DESC)
 
My issue is this - when there are multiple orders tied for Top N, it lists the customer multiple times (see attached).  I would like to have it sum up the entries for each customer.  So, for the attached pic - I want it to show Bierman Contracting, Inc. * only once and show 375 for the tons. 
 
Thank you in advance for any guidance you can give me!
Donna
Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    NonnaBlue you are almose there, store TOPN in a variable and then in next variable summarize TOPN variable over customer and sum value and then use that in concatenatex:

     

    Measure = 
VAR __topn = <your topn logic>
VAR __table = SUMMARIZE ( __topn, [Customer], "@Sum", SUM ( Table[Value] ) )
RETURN <concatenatex logic>

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

     

     

1 Reply

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    NonnaBlue you are almose there, store TOPN in a variable and then in next variable summarize TOPN variable over customer and sum value and then use that in concatenatex:

     

    Measure = 
VAR __topn = <your topn logic>
VAR __table = SUMMARIZE ( __topn, [Customer], "@Sum", SUM ( Table[Value] ) )
RETURN <concatenatex logic>

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.