Forum Discussion

Manuel290's avatar
Manuel290
New Member
6 years ago
Solved

Problem to use TOPN or RANKX along with a stacked column chart with age range as Legend

Hi everybody,   My problem is fairly simple in my opinion, but I've been stuck on it for quite some time. I have a table of Clients, with information such as ID, the date of their first purchase on...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Manuel290 

     

    You may try the following measure to see if it helps.

    Top 100 clientes por LTV por idade =
VAR _month =
    SELECTEDVALUE ( Client[Month] )
VAR _age =
    SELECTEDVALUE ( Clientes[Faixa etária] )
RETURN
    CALCULATE (
        COUNT ( Clientes[ID Cliente] ),
        FILTER (
            TOPN (
                100,
                FILTER ( ALL ( Clientes ), Client[Month] = _month ),
                Clientes[LTV primeiros 30 dias], DESC
            ),
            Clientes[Faixa etária] = _age
        )
    )
        / CALCULATE (
            COUNT ( Clientes[ID Cliente] ),
            TOPN (
                100,
                FILTER ( ALL ( Clientes ), Client[Month] = _month ),
                Clientes[LTV primeiros 30 dias], DESC
            )
        )

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi, Manuel290 

 

You may try the following measure to see if it helps.

Top 100 clientes por LTV por idade =
VAR _month =
    SELECTEDVALUE ( Client[Month] )
VAR _age =
    SELECTEDVALUE ( Clientes[Faixa etária] )
RETURN
    CALCULATE (
        COUNT ( Clientes[ID Cliente] ),
        FILTER (
            TOPN (
                100,
                FILTER ( ALL ( Clientes ), Client[Month] = _month ),
                Clientes[LTV primeiros 30 dias], DESC
            ),
            Clientes[Faixa etária] = _age
        )
    )
        / CALCULATE (
            COUNT ( Clientes[ID Cliente] ),
            TOPN (
                100,
                FILTER ( ALL ( Clientes ), Client[Month] = _month ),
                Clientes[LTV primeiros 30 dias], DESC
            )
        )

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Manuel290's avatar
    Manuel290
    New Member
    6 years ago

    Thank you soo much! This worked perfectly, just as I wanted. The only thing I changed was the _month VAR. Instead of a month column, I used as a filter a Cohort Month column I already had. In that way, I was able to filter considering not only the month, but also the year, which is necessary for me. The other little change I did was to add another filter outside the TOPN. It was a filter to take out clients who had [Faixa etária] = BLANK(). I took that out because it was no use for me, as I was interested only in the % of each age range, not the total value.

    Thank you again!