Forum Discussion

GreenP's avatar
GreenP
Regular Visitor
3 years ago
Solved

TOPN excluding blank measure

Hi Experts,

I have a table like below:

Dealer Target Actual
Dealer 013 82 52
Dealer 012 72 32
Dealer 018   13
Dealer 010 54 10
Dealer 014 32 5
Dealer 016 79 -10
 Dealer 01 37  
 Dealer 02 25  
 Dealer 03 23  
 Dealer 04 28  
 Dealer 05 99  
 Dealer 06 66  
 Dealer 07 23  
 Dealer 08 18  
 Dealer 09 15  
Dealer 011 48  
Dealer 015 49  
Dealer 017 40  

 

I am trying to show the total actual sales by top 10 dealers.

I am using the following dax:

 

Total Sales(Top 10 Dealers) = CALCULATE(
    SUM(table[Actual]),
    TOPN(
        10,
        ALL(table[Dealer]),
        CALCULATE(SUM(table[Actual]))
    )
)
 
This is returning 112 instead of 102 as it is considering the blank values as well in top 10 and thus ignores the negative value in the column.
 
How can we ignore the blank measures in the TOPN function?
blank
DAX
TopN
  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Total sales ( top 10 dealers ) =
VAR DealersWithSales =
    FILTER (
        ADDCOLUMNS (
            ALL ( 'Table'[Dealer] ),
            "@sales", CALCULATE ( SUM ( 'Table'[Actual] ) )
        ),
        NOT ISBLANK ( [@sales] )
    )
VAR Top10Dealers =
    TOPN ( 10, DealersWithSales, [@sales] )
VAR Result =
    SUMX ( Top10Dealers, [@sales] )
RETURN
    Result

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    GreenP Try:

    Total Sales(Top 10 Dealers) = 
  VAR __Table =
    TOPN(
        10,
        FILTER(ALL('Table'),[Actual] <> BLANK()),
        CALCULATE(SUM('Table'[Actual]))
    )
  VAR __Result = SUMX(__Table,[Actual])
RETURN
  __Result

    PBIX attached below signature. 

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    Total sales ( top 10 dealers ) =
VAR DealersWithSales =
    FILTER (
        ADDCOLUMNS (
            ALL ( 'Table'[Dealer] ),
            "@sales", CALCULATE ( SUM ( 'Table'[Actual] ) )
        ),
        NOT ISBLANK ( [@sales] )
    )
VAR Top10Dealers =
    TOPN ( 10, DealersWithSales, [@sales] )
VAR Result =
    SUMX ( Top10Dealers, [@sales] )
RETURN
    Result