locked
SUM of top 10 - split by client and event RRS feed

  • Question

  • Hi All

    I have a tabular model connected to excel 2013 with the following columns:

    ReportClientName (This is the client)

    EventDescription - this doesn't always have an event associated with it - some are blank. Some events have multiple rows with the same code

    USDValue (The dollar amount)

    I want to write a dax query to get the top 10 dollar amounts for each ReportClientName, split by eventdescription in USDValue descending

    So the result I want is:

    Client A
            Top 10 events with their summed dollar amt

    Client B

         "" Same as above

    Does anyone know how to achieve this? Thanks alot

    Wednesday, September 9, 2015 8:09 AM

Answers

  • If you want to create your reports in Excel, the pivot table options would take you quite far (incl. ranking). This would give you the chance to reduce the number of measures in your model.

    But in order to help you with this I need a picture of how your report should look like. Also how your source data looks like.

    I think you could also create measures in DAX who do the filtering there. But this is not my area of expertise.


    Imke Feldmann TheBIccountant.com

    • Proposed as answer by Charlie Liao Monday, September 21, 2015 8:36 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:54 AM
    Wednesday, September 9, 2015 4:39 PM
    Answerer

All replies

  • Hi James,

    I think that you just need to apply a Top-10 Value-Filter on EventDescription in your Excel-Pivot-table.

    Not sure if I understood you right, but if there are multiple blanks in the Event-Description the Pivot table will always aggregate on them. So you might need to take the EventKey in there as well.

    LinkToVideo


    Imke Feldmann TheBIccountant.com

    Wednesday, September 9, 2015 10:24 AM
    Answerer
  • That won't work

    It won't sort them into value descending order! I need it ranked and split by USD value

    Wednesday, September 9, 2015 4:07 PM
  • If you want to create your reports in Excel, the pivot table options would take you quite far (incl. ranking). This would give you the chance to reduce the number of measures in your model.

    But in order to help you with this I need a picture of how your report should look like. Also how your source data looks like.

    I think you could also create measures in DAX who do the filtering there. But this is not my area of expertise.


    Imke Feldmann TheBIccountant.com

    • Proposed as answer by Charlie Liao Monday, September 21, 2015 8:36 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:54 AM
    Wednesday, September 9, 2015 4:39 PM
    Answerer