Power BI - Word Cloud - Top N Words

Question

I have a question regarding "Word Cloud" custom visual. Let's say in my table, I have a column for "Keyword", "Cost" and "Clicks". I then create the following measures:

[Total Cost] = SUM(Table[Cost])
[Total Clicks] = SUM(Table[Clicks])
[CPC] = DIVIDE([Total Cost], [Total Clicks]) // Cost-per-click

I then add the Table[Keyword] in the "Category" section of Word Cloud visual, and add [CPC] in the "Values" section. All works fine, I see the keywords and size of the keyword is based on [CPC]. Bigger the CPC, bigger the size of the word and vice versa.

Question: Let's say I have 1000s of keywords in my Table. But I only want to show Top 20 keywords based on the CPC, also the keyword with lowest CPC should be the biggest and keyword with the highest CPC be the smallest.

Thanks, Shiv

Answer 1

Since you want the lowest [CPC] to be the largest, I'd suggest defining a measure that is the reciprocal (multiplicative inverse) of [CPC].

Size = DIVIDE(1, [CPC])

(If this doesn't size exactly how you want, try applying a function to [Size] like EXP or LOG or POWER or SQRT.)

To only show the Top 20 keywords, just use Top N filtering on the keyword based on your [Size] measure.