TOPN excluding blank measure

[GreenP]

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?

[johnt75 · Accepted solution]

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

[Greg_Deckler]

@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.