Top 80% Engagement

[qjc171819]

Hi guys,

I have a table with Item ID and Engagement. Now i would like to create another table that shows which items that make up 80% of total engagements, also these items should desc by engagement.

The original table:

What i want to create:

ps: 220*80%=176. From the table, item C, D, A ,79+60+46=185>176, but 79+60=139 less than 80 percent.

Any advice?

Regards 

Jesse

[vanessafvg · Accepted solution]

ive never done this before but maybe this will work

@qjc171819

engagementmeasure = sum(engagment)

incrementalengagement =
VAR CurrentEngagement = Table[engagementmeasure]
RETURN
    SUMX (
        FILTER ( table, table[engagementmeasure] >= incrementalengagement ),
        table[enagementmeasure]
    )

Table[IncrementPCT] = 

DIVIDE (table[incrementalengagement], 

sum (engagementmeasure))

then based on that result theoretically you can create a table where the Table[IncrementPCT] <= 0.8

[GIO200011]

Hello,

But with this approaching, you have less than 80 %. If I want to cover that 80 %, what should I do? Without manually change the 80 % for the value of the next Item.

I need to know the clients that are the 80 %, but it must be at least 80 %, If I set less or equal than 80 %, this approaching will only be correct when the clients do exactly 80 %, but if they do 81 %, it will take earlier item (maybe 78 %). 

Any suggestion?

Regards!

[Ashish_Mathur]

Hi,

Will this work for you?  Download the PBI file from here.

[Anonymous]

This is a great idea! Thank you!

[Anonymous]

Hello @Ashish_Mathur,

the solution you provided fits to my problem. But I need to include a time filter as well.

I reconstructed your solution. The Ranking works. But the cumulative total doesn´t work.

Kumulativer Umsatz = SUMX(TOPN([Pos. Debitoren];CALCULATETABLE(VALUES(Kalender[Datum]);ALL(Kalender[Datum]));[Absoluter Umsatz]);[Absoluter Umsatz])

I add my measures to your code.

Pos. Debitoren = Rank 

Kalender[Datum] = Date 

[Absoluter Umsatz) = Total

Do you have an idea to solve my problem ?

Thanks for your help!

Christian

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[Anonymous]

Hi,

thanks for your answer.

Your solution works perfectly. I added the wrong columns to the mesure. So it was just my fault.

Sorry for that

Christian

[Anonymous]

Hi,

thanks for your answer.

Your solution works perfectly. I added the wrong columns to the mesure. So it was just my fault.

Sorry for that

Christian

[GIO200011]

 What I needed was the number of the clients, in your example the measure would be "3".

So I get the approach with this:

I have a CalculateTable that summarizes the clients' name by month (that is why I use CALCULATETABEL and ALLEXCEPT with the calendar table in my measures) and also ranked them by volume. 

So in this table I created a cummulative measure and total volume (no matter what filter apllies, only filters from the calendar table).

Cumulative Volume_RMX = CALCULATE(SUM(Rank_clientes_RMX[Volumen]),
                            FILTER(
                                CALCULATETABLE(Rank_clientes_RMX,ALLEXCEPT(Rank_clientes_RMX, Calendario, Rank_clientes_RMX[Calendar YearMonth])),
                                Rank_clientes_RMX[Rank]<=MAX(Rank_clientes_RMX[Rank])
                                )
                            )

volume_all_month_RMX = CALCULATE(SUM(Rank_clientes_RMX[Volumen]),ALLEXCEPT(Rank_clientes_RMX,Calendario,Rank_clientes_RMX[Calendar YearMonth]))

Then I created the final measure

Clientes 80 % vol. = CALCULATE(COUNTROWS(Rank_clientes_RMX),
                                    ALLEXCEPT(Rank_clientes_RMX, Calendario, Rank_clientes_RMX[Calendar YearMonth]),
                                    FILTER(
                                        CALCULATETABLE(Rank_clientes_RMX, ALLEXCEPT(Rank_clientes_RMX, Calendario, Rank_clientes_RMX[Calendar YearMonth])),
                                        [Cumulative Volume_RMX]<[volume_all_month_RMX]*0.8
                                    )
                                )+1

The final measure, get the number of clients that makes less than 80 % of the total volume + 1. 

I hope it helps someone.

Regards,

Giovany

[qjc171819]

You must be a genius. 

Thanks for great help.

@vanessafvg