Forum Discussion

alialsayer's avatar
alialsayer
Helper I
5 years ago
Solved

TopN

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 !!!

 

statecustomer nameSales $RankingVisible viewTop X
OhioName 12,839,286112,839,286
OhioName 22,371,607212,371,607
OhioName 31,446,463311,446,463
OhioName 4639,00041639,000
OhioName 5224,32951224,329
OhioName 6207,00061207,000
OhioName 7179,00071179,000
OhioName 8154,80081154,800
OhioName 9150,00091150,000
OhioName 10110,000101110,000
OhioName 1181,84011-1 
OhioName 1275,000120 
OhioName 1374,000130 
OhioName 1470,000140 
OhioName 1570,000140 
OhioName 1659,700160 
OhioName 1750,000170 
OhioName 1829,200180 
OhioName 1922,800190 
OhioName 2015,000200 
OhioName 219,900210 
OhioName 229,000220 
OhioName 234,050230 
OhioName 243,643240 
OhioName 252,000250 
OhioName 261,000260 
OhioName 27198270 
OhioName 281280 

 

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

 

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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

     

     

2 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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