Forum Discussion
Top N Function and BLANK()
Here is one approach to consider:
Top 100 Customers =
CALCULATE( [Total Sales],
TOPN( 100, ALL(Customer_Lookup[customer names]), [Total Sales], DESC),
VALUES( Customer_Lookup[customer names] ))
Top 100 Customers =
VAR summarytable =
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Customer_Lookup, Customer_Lookup[customer names] ),
"@sales", [Total Sales]
),
[@sales] > 0
)
VAR top100 =
TOPN ( 100, summarytable, [@sales], DESC )
RETURN
SUMX ( top100, [@sales] )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
This actually does solvea problem, but not the one he is asking to be solved. I only know this because I was using this as a reference for the same problem (almost). However, while your solution does remove those that have a value < 1, it does not account for customers that cannot be matched (the don't exist, thus they are blank). So when throwing the customer dimension on a stacked column chart, you have a stack of (blank) values that have no customer to attach to.
So top 10 of customers looks something like
Jon,Joe,Jerry,Jake,Sally,Cindy,Karen, Bob,Tom, (blank)
or values, 122,101,99,94,88,82,79,65,54,51
SO... How does one exclude the blank "Customer" from the stacked column chart?
