Forum Discussion
Is this Dynamic Value segmentation?
Hi ING_BT ,
Based on the description you have you need to do a measure similar to this one:
Count Words on ranking = VAR temptable = SUMMARIZE ( words, Words[Brand], Words[Keyword], "AVerageRanking", AVERAGE ( Words[Rank] ) ) RETURN COUNTROWS ( FILTER ( temptable, [AVerageRanking] <= MAX ( Ranking[End] ) && [AVerageRanking] >= MIN ( Ranking[Start] ) ) )I have added some words counts on the Brand A and has you can see the total word count is 2 always:
Be aware that this calculation is at a brand level if your try to do it with all brands at all ranking the calculation is incorrect has you can see in the Total column below
Hi ING_BT ,
How would this work if the word without ranking have a ranking on another week?
Pciking up your example:
| Brand | Keyword | Rank |
|
A |
Label | 3 |
| A | Tutorial | 2 |
| B | Label | 43 |
| B | Tutorial | 4 |
| C | Label | 6 |
| C | Tutorial | 21 |
| C | Label | |
| C | Other |
How would you consider the Label without ranking on this case would you have the average of the label has 3 since is (6 + 0) / 2 and or would you consider only once label in this case for Company C you would have the wich of the tables below:
Table 1 - Consider only words above 50 the ones that have no ranking (work: Other) all others words are consider the average of the values with ranking (Label - 6 , Tutorial - 21)
| Interval | Words |
|
1 - 3 |
0 |
| 4 - 10 | 1 |
| 11 - 20 | 1 |
| 21 - 49 | 0 |
| +50 Others | 1 |
Table 2 - Consider only words above 50 the ones that have no ranking (work: Other) all others words are consider the average of the values (Label - 6/2) , Tutorial - 21)
| Interval | Words |
|
1 - 3 |
1 |
| 4 - 10 | 0 |
| 11 - 20 | 1 |
| 21 - 49 | 0 |
| +50 Others | 1 |
Table 3 - Consider words above 50 the ones that have no ranking (work: Other + Label) all others words are consider the average of the values (Label - 6 , Tutorial - 21)
| Interval | Words |
|
1 - 3 |
0 |
| 4 - 10 | 1 |
| 11 - 20 | 1 |
| 21 - 49 | 0 |
| +50 Others | 2 |
Wich option do you consider? I know that in the last one we have more words than the distinct words but is to understand if you want to highlight that some words may need to be revised on the ranking.
I think the Table 2 is the one I am looking for.
As, if for the selected period (with several dates), a brand has no rank at all on 10 keywords; I need to display these 10 keywords inside that +50 Not Ranked interval.
However, if there is a keyword where it has as rank but only on a few dates, it should be counted in the average, example:
Label is in the interval (2+2+10)/3=4.6 => 4 to 10
(empty are (null) and not "0" in the data, which simplyfies things?)
| 10/12 | Label | (null) |
| 16/12 | Label | 2 |
| 21/12 | Label | 10 |
| 27/12 | Label | 2 |
| 31/12 | Label | (null) |
(appologiesfor the late answer)
Thank you!
Sorry for the additional question but on this case of label will label show up in the no label words or since it was counted in the rank it wont count again?
Indeed, as the measure "average" for the label will show a valide outcome "4.6" it will not be counted again in the "Not ranked", as it should display only average = (null) (as it the keyword has no rank on any of the selected dates)!
Does it help?Hi ING_BT ,
Try the following measure:
Count Words on ranking = VAR temptable = SUMMARIZE ( FILTER ( words, NOT ( ISBLANK ( Words[Rank] ) ) ), Words[Brand], Words[Keyword], "AVerageRanking", AVERAGE ( Words[Rank] ) ) VAR temptable_Blanks = FILTER ( SUMMARIZE ( words, Words[Brand], Words[Keyword], "AVerageRanking", AVERAGE ( Words[Rank] ) ), [AVerageRanking] = 0 ) VAR WordsWithRank = COUNTROWS ( FILTER ( temptable, [AVerageRanking] <= MAX ( Ranking[End] ) && [AVerageRanking] >= MIN ( Ranking[Start] ) ) ) RETURN IF ( HASONEVALUE ( Ranking[Sort] ) && SELECTEDVALUE ( Ranking[Sort] ) <> "+50 and Others", WordsWithRank, WordsWithRank + COUNTROWS ( temptable_Blanks ) )