Sum the total sales for just Top n sales Customers


#1

Customer Top 15

1 $60,630.08
2 $19,038.00
3 $13,452.00
4 $12,531.00
5 $11,045.00
6 $10,893.00
7 $10,485.00

Grand Total $302,902.65

My measures

1. Rank:=IF(
HASONEVALUE(
Sales[CustomerName]),
                 RANKX(
                 ALLSELECTED(Sales[CustomerName]),
                  [Total Invoiced],,DESC),
BLANK())

2. Top 7=IF([Rank]<=7,
                 [Total Invoiced],
                    BLANK())

The grand total should be=$138074.0 8
I tried to use summarize but I am stuck

Thanks


#2

Does this post help you at all?

The Total value is not the sum of the rows above it, it is calculated on its own, so the result can be unexpected if you don’t understand this aspect. A Google search will find many explanations of how to understand this.

Cheers
Phil


#3

@Marta,
What if you wrapped the calculation in a SUMX( VALUES like the following: ( I find it easier and more productive to create these measures separately and then put them together)

Start with your based measure. Total Sales in this case:
Total Sales = SUM( FactOnlineSales[SalesAmount] )

Then create the RankX measure:
Rank = RANKX( ALL( DimCustomer), [Total Sales],,DESC,Dense )

Then the final measure to get the correct totals:
Top 10 with Correct Total = SUMX(VALUES(DimCustomer[CompanyName]),IF( [Rank] <= 10, [Total Sales]))

Final%20Matrix

Enterprise%20DNA%20Expert%20-%20Small


#4

Thanks Nick
It worked
I also used this other pattern

Top 10 Sales=

VAR RankingContext=VALUES('Sales[Customer Name])

Return
CALCUALTE ([Total Sales]),
TOPN (10,
ALL(Sales[Customer Name]),
[Total Sales]),
RankingContext)

Thnaks


#5

@Marta,
Glad you got it to work. Many ways to solve these problems for sure! That’s what makes it fun…and frustrating at times :sunglasses:

Enterprise%20DNA%20Expert%20-%20Small