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

[Manuel290]

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 the platform, age range (faixa etária, in my language) and LTV over the first 30 days after the first purchase. (LTV primeiros 30 dias is the name of the column). LTV stands for Life Time Value, which is the amount the client has spent with our products.

There are more columns than that, but those are the ones which matter for me in this particular issue.

What I am trying to do is to generate a stacked column chart which has:
Axis - categorized by the month of the 1st purchase

Legend - Age range

Value - Count of clients, shown in a % over the total of the column.

I've been able to do that, and this is my result:

This is what I wanted, and it is correct. However, the problem arises when I try to replicate this, but I want all of this information on the chart to be only about the TOP 100 customers of the month in which they joined, using the previously mentioned LTV as the value for which they will be ranked.

Therefore, I think my logic is: 
Somehow filter, for each month, the TOP 100 clients by the highest LTV --> Given those 100 clients, display their infos about age according to the same logic in the chart above. 

I've tried to use that logic, but it is not working, and I've seen other threads, but I haven't found a case similar to mine. The closest I got to what I wanted was using the following formula:

Top 100 clientes por LTV 2 = 
CALCULATE(
    CALCULATE(COUNT(Clientes[ID Cliente]),
        TOPN(100,Clientes,Clientes[LTV primeiros 30 dias],DESC)),
KEEPFILTERS(Clientes[Faixa etária] <> BLANK())
)

And then this one, to make it as as % of the month column

Top 100 clientes por LTV por idade = 
[Top 100 clientes por LTV 2]/CALCULATE([Top 100 clientes por LTV 2],ALL(Clientes[Faixa etária]))

The results were the following:

As you can see, it is not correct. The problem, however, is the "Top 100 clients por LTV 2" formula, not the one with the %. I know that because, ploting a visual for that one, what I get is:

This is not what I imagine it should come as a result. In theory, what I wanted was for each bar to be stacked until 100, and each one would be divided according to the customers age range. However, I can't understand what is going on inside the formulas I wrote.

If anyone could help me, that would be great, thanks

[v-alq-msft · Accepted solution]

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]

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!