cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ING_BT
Helper I
Helper I

Is this Dynamic Value segmentation?

Hello ! 

 

I am facing an issue, I'm sure the solution is simple, but lacking the knowledge I'm struggling to find the right way to do it. 

 

Here's the setup: 

I have a table with a list of "Keywords", each keyword is tracked for several brands on a weekly basis. 

So each week, for each brand, every keywords is duplicated and we get the "Rank" of the brand on the keyword. 

 

Like so: 

BrandKeywordRank

A

Label3
ATutorial2
BLabel43
BTutorial4
CLabel6
CTutorial21

(without the date (week) column in the example)

 

I want to create the followying table filtered for only one Brand

Rank IntervalsNumber of Keywords
1 to 3 123
4 to 6 234
7 to 10345
11 to 20456
21 to 49567
50+ (not ranked)678

 

The total number of keyword needs to match the total distinct number of keywords;

To do so, because the rank changes everyweek for a keywords, we need to calculate the AVERAGE rank of the keywords on the selected period and dynamicaly segment it in the right segment!

 

Keywords, rank, brand and weeks are in the same table. 

Calendar table is used.

I have created a table with intervals with min and max columns.

 

I also tried this measure (found on the community) however, it used the column "rank" as is, instead of doing an average? And thus One keyword is counted several times in several segments (if we select a 1 year date range for example)

 

Calculate (DISTINCTCOUNT('Rankings Evolution'[Keyword]),

FILTER(
    VALUES('Rankings Evolution'[Rank avg]),
        COUNTROWS(
            FILTER(Intervals,

    'Rankings Evolution'[Rank avg] >= Intervals[Min]
    && 
    'Rankings Evolution'[Rank avg] < Intervals[Max]
    ))>0))

 

 

Thank you in advance!! 

 

Best,

BT

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

MFelix_0-1641512077461.png

 

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 

MFelix_1-1641512179383.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

10 REPLIES 10
MFelix
Super User
Super User

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:

MFelix_0-1641512077461.png

 

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 

MFelix_1-1641512179383.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello Félix, 

 

Thank you so much for your help! Your solution is very clear and It works perfectly. And thank you for very detailed explanation. 

 

I spend hours to solution this, you cannot imagine the relief! I understand the logic now (thank you for naming it "temptable", makes it so obvious now!)

All the best,

BT

 

Hi @ING_BT ,

 

Has I refer the the calculation is made at brand level, if you need to redo this to have all the brands selected and all the words this can be changed.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello Félix,

Yes I understand, thank you! 


If I may, I have another question concerning the measure: 
How would you change the measure to include all the "Empty ranks" which are considered as "Not Ranked" into a "Not Ranked" interval ?
(obviously this doens't work with the <Max & >= MIN) 
ING_BT_0-1641574397065.png


It's basicaly to classify everything that as the average = blank()

ISBLANK(AVERAGE ( Words[Rank] ))


(PS: it's normal that I put 50 as max in the 21 to 49 formula - the data is not the cleanest) 

Best,
BT

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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/12Label(null)
16/12Label2
21/12Label10
27/12Label2
31/12Label(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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 )
    )

MFelix_0-1641581564079.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Wow. Just wow, it works perfectly! You are a genius and a lifesaver (well project saver haha) 

Thank you so much for your time! 😎

 

Have a great weekend, 

All the best,

Bt.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors