TopN

[alialsayer]

Dears, 

I just need help as I am trying to find out the following 

1. Top X customer name by sales & at each state
2. Total Top X by country each state should have Top X customers (for example Ohio top 10 customers sales are 400$ , California have 10 customers sales are 300$ the total wil be 700 $)
3. Top X percentage for each state which is Top X / total sales

my problem is when I write DAX for the top 10 individually it is correct but the total it gives me total sales !!!

state	customer name	Sales $	Ranking	Visible view	Top X
Ohio	Name 1	2,839,286	1	1	2,839,286
Ohio	Name 2	2,371,607	2	1	2,371,607
Ohio	Name 3	1,446,463	3	1	1,446,463
Ohio	Name 4	639,000	4	1	639,000
Ohio	Name 5	224,329	5	1	224,329
Ohio	Name 6	207,000	6	1	207,000
Ohio	Name 7	179,000	7	1	179,000
Ohio	Name 8	154,800	8	1	154,800
Ohio	Name 9	150,000	9	1	150,000
Ohio	Name 10	110,000	10	1	110,000
Ohio	Name 11	81,840	11	-1
Ohio	Name 12	75,000	12	0
Ohio	Name 13	74,000	13	0
Ohio	Name 14	70,000	14	0
Ohio	Name 15	70,000	14	0
Ohio	Name 16	59,700	16	0
Ohio	Name 17	50,000	17	0
Ohio	Name 18	29,200	18	0
Ohio	Name 19	22,800	19	0
Ohio	Name 20	15,000	20	0
Ohio	Name 21	9,900	21	0
Ohio	Name 22	9,000	22	0
Ohio	Name 23	4,050	23	0
Ohio	Name 24	3,643	24	0
Ohio	Name 25	2,000	25	0
Ohio	Name 26	1,000	26	0
Ohio	Name 27	198	27	0
Ohio	Name 28	1	28	0

and when I have applied Dax it gives me Top 10 equal to 8,898,817 while it should be 8,321,485

Top X  =

VAR TopV = SELECTEDVALUE('Parameter Top X'[Top X])

Return

SWITCH(TRUE(),

TopV = 0 , [Sales $], 

RANKX(

ALLSELECTED(

Produc[Customer name],

Product[SaleType],

Product[status],

Product[unique deal]

),

[Sales $],,DESC,Skip) <= TopV ,

[Sales $]

)

note : 

'Parameter Top X'[Top X] is a parameter I added to change the number of Top N

[alialsayer]

Thanks, @v-luwang-msft for your professionality, yes this is what I am looking for i appreciate your help thanks again 

[v-luwang-msft · Accepted solution]

Hi @alialsayer ，

Hi 

Pls try the following measure：

1.Top X customer name by sales & at each state

Top test = 
VAR TopV =
    SELECTEDVALUE ( 'Parameter Top X'[Top X] )
VAR test2 =
    RANKX (
        ALL ( 'Product' ),
        CALCULATE ( SUM ( 'Product'[Sales $] ) ),
        ,
        DESC,
        DENSE
    )
VAR test3 =
    IF ( test2 > SELECTEDVALUE ( 'Parameter Top X'[Top X] ), BLANK (), test2 )
RETURN
    test3

2.Total Top X by country each state should have Top X customers (for example Ohio top 10 customers sales are 400$ , California have 10 customers sales are 300$ the total wil be 700 $)

Top sumsales = 
VAR TopV =
    SELECTEDVALUE ( 'Parameter Top X'[Top X] )
VAR test4 =
    CALCULATE (
        SUM ( 'Product'[Sales $] ),
        FILTER (
            ALL ( 'Product' ),
            RANKX (
                ALL ( 'Product' ),
                CALCULATE ( SUM ( 'Product'[Sales $] )),
                ,
                DESC,
                DENSE
            )
                <= SELECTEDVALUE ( 'Parameter Top X'[Top X] )&&'Product'[state]=MAX('Product'[state]))
        )
    
RETURN
    test4

3.Top X percentage for each state which is Top X / total sales

Top sumsalespercnt = 
VAR TopV =
    SELECTEDVALUE ( 'Parameter Top X'[Top X] )
VAR test1 =
    CALCULATE (
        SUM ( 'Product'[Sales $] ),
        FILTER (
            ALL ( 'Product' ),
            RANKX (
                ALL ( 'Product' ),
                CALCULATE ( SUM ( 'Product'[Sales $] ) ),
                ,
                DESC,
                DENSE
            )
                <= SELECTEDVALUE ( 'Parameter Top X'[Top X] )
                && 'Product'[state] = MAX ( 'Product'[state] )
        )
    )
VAR test2 =
    CALCULATE (
        SUM ( 'Product'[Sales $] ),
        FILTER ( ALL ( 'Product' ), 'Product'[state] = MAX ( 'Product'[state] ) )
    )
VAR TEST3 =
    DIVIDE ( test1, test2, 4 )
RETURN
    TEST3

You could download my pbix file if you need!

WIsh it is helpful for you!

Best Regards

Lucien