Forum Discussion
Top N Function and BLANK()
Hi - I created a measure for the top 100 Customers.
See below:
Top 100 Customers =
CALCULATE( [Total Sales],
TOPN( 100, ALL(Customer_Lookup[customer names]), [Total Sales], DESC),
VALUES( Customer_Lookup[customer names] ))
I want to be able to recognise situations where I may not have up to 100 customers and not have 0 numbers showing in the bar chart I have created.
I know there is a BLANK() function that exists however I am unsure where to include this in my measure above.
Please advise or any other tips would be useful
Many thanks
4 Replies
atin , Refer if this can help
https://community.powerbi.com/t5/Desktop/RANKX-excluding-blanks/td-p/396044
https://community.powerbi.com/t5/Desktop/RANKX-Ignore-blank-zero-across-time/td-p/436949
https://community.powerbi.com/t5/Desktop/RANKX-without-zeros-and-blanks/td-p/599558For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415Many thanks for your response - I had a look at the links you have suggested and does not solve my question.
My question is probably not very clear and will rephrase this. What I am looking to achieve is:
create a Top N measure using the CALCULATE Function and use BLANKS to exclude blank numbers
See Measure below:
Top 100 Customers =
CALCULATE( [Total Sales],
TOPN( 100, ALL(Customer_Lookup[customer names]), [Total Sales], DESC),
VALUES( Customer_Lookup[customer names] ))
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?
