locked
Sum of Top3 Values RRS feed

  • Question

  • Imagine we have a similar dataset: 

    I would like to create a measure using DAX that shows the total revenue for Top 3 countries only, from all companies of course!

    I would like to know whether is is possible to do it in one formula. So far I did it in a couple of steps, but the question is whether a formula is possible to create that automatically figures out top 3 countries and then displays the sum of all revenues from these.

    Your help will be much appreciated

    Country Revenue Company
    DE 500  A
    US 400  A
    GB 300  A
    FR 200  A
    ES 100  A
    US 620  B
    GB 420  B
    ES 520  B
    LV 120  B
    FR 320  B
    DE 570  C
    GB 680  C
    US 215  C
    ES 350  C
    FR 485  C
    Wednesday, July 12, 2017 8:07 AM

Answers

  • What you are trying to accomplish is not difficult, cramping all this logic into one measure this is what makes it more challenging but possible as you can see below.


    Total Revenue Top 3 Countries =
    VAR TopThreeCountries =
        TOPN (
            3,
            ADDCOLUMNS ( Table1, "SalesRank", RANKX ( ALL ( Table1 ), [Sales Amount] ) ),
            [Sales Amount]
        )
    RETURN
        CALCULATE (
            CALCULATE ( SUM ( Table1[Revenue ] ), ALLEXCEPT ( Table1, Table1[Country ] ) ),
            FILTER (
                TopThreeCountries,
                [SalesRank] IN { 1, 2, 3 }
            )
        )
         
    *** [Sales Amount] is a base measure  SUM(Table1[Revenue]) I am assuming you already have it in your model. 








    Monday, July 17, 2017 6:06 PM

All replies

  • Hi Constantine,

    Thanks for your quesiton.

    I agree with you, it would be much easier to achieve this through sevral steps then do it in one formula.

    I have tried many different ways, but failed to accomplish this in one formula finally. I guess the reason for this is that we need a calculated column Rank to identify the top 3.

    Following are steps you may want to refer:

    Create a table with below DAX:
    Table =
    SUMMARIZECOLUMNS (
        Revenue[Country],
        "Revenue", CALCULATE (
            SUM ( Revenue[Revenue] ),
            FILTER ( VALUES ( Revenue[Country] ), Revenue[Country] = Revenue[Country] )
        )
    )

    Add a calculated column called Rank as below:
    rank = RANKX('Table','Table'[Revenue])

    Then you can create a measure called top3 as below:
    Top3 = sumx(filter('Table','Table'[rank]<=3),'Table'[Revenue])

    Below DAX formula is something you may also want to refer, although it is not working:
    SUMTop3 =
    SUMX (
        TOPN (
            3,
            SUMMARIZECOLUMNS (
                Revenue[Country],
                "NewRevenue", CALCULATE ( SUM ( Revenue[Revenue] ), ALLEXCEPT ( Revenue, Revenue[Country] ) )
            ),
            [NewRevenue]
        ),
        [NewRevenue]
    )

    TotalWrong =
    IF (
        CONTAINS (
            TOPN (
                3,
                SUMMARIZE (
                    ALL ( Revenue ),
                    Revenue[Country],
                    "Revenue", SUM ( Revenue[Revenue] )
                ),
                [Revenue], DESC
            ),
            Revenue[Country], MAX ( Revenue[Country] )
        ),
        SUM ( Revenue[Revenue] ),
        0
    )
    
    SUMXTop3 =
    SUMX (
        TOPN (
            3,
            SUMMARIZE ( Revenue, Revenue[Country], "Revenue", SUM ( Revenue[Revenue] ) ),
            [Revenue], DESC
        ),
        [Revenue]
    )




    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com




    Thursday, July 13, 2017 3:53 AM
  • What you are trying to accomplish is not difficult, cramping all this logic into one measure this is what makes it more challenging but possible as you can see below.


    Total Revenue Top 3 Countries =
    VAR TopThreeCountries =
        TOPN (
            3,
            ADDCOLUMNS ( Table1, "SalesRank", RANKX ( ALL ( Table1 ), [Sales Amount] ) ),
            [Sales Amount]
        )
    RETURN
        CALCULATE (
            CALCULATE ( SUM ( Table1[Revenue ] ), ALLEXCEPT ( Table1, Table1[Country ] ) ),
            FILTER (
                TopThreeCountries,
                [SalesRank] IN { 1, 2, 3 }
            )
        )
         
    *** [Sales Amount] is a base measure  SUM(Table1[Revenue]) I am assuming you already have it in your model. 








    Monday, July 17, 2017 6:06 PM
  • Vishe,

    I would like to DISPUTE your decision of marking this question as answered by Willson. The customer has explicitly pointed out that the solution should be in one formula .

    Here is the quote "I would like to know whether is is possible to do it in one formula."

    My solution does exactly what's requested which provides the solution in one formula.  Now, I mean no disrespect to Willson but for this particular post everyone would agree my solution deserves proper credit. 

    Thanks, Nick







    Wednesday, August 2, 2017 9:28 PM
  • Hi Nick,

    I've cleared the abuse flag as Wilson's solution being marked as an answer isn't an actual conduct violation.

    Having reviewed little.belowski's original post, you're right that one of the requirements for a valid solution is that the logic is handled by a single DAX formula.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Thursday, August 3, 2017 10:07 PM
  • Hello Michael,

    The abuse flag was a just a cry out for someone to take a deeper look into this. I apologize for resorting to dirty tactics but sometimes it's the only option available.

    Thank you for your help.

    N-

    Friday, August 4, 2017 1:24 PM