# Sum of Top3 Values • ### 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

• 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,

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,

• 