Forum Discussion

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

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

Count_Words.pbix23 KB

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