Forum Discussion

ING_BT's avatar
ING_BT
Helper I
4 years ago
Solved

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 "Ke...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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 

     

     

ING_BT's avatar
ING_BT
Helper I
4 years ago

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!

MFelix's avatar
MFelix
Super User
4 years ago

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?

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

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

     

  • ING_BT's avatar
    ING_BT
    Helper I
    4 years ago

    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?

  • ING_BT's avatar
    ING_BT
    Helper I
    4 years ago

    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.