Forum Discussion

qjc171819's avatar
qjc171819
Frequent Visitor
9 years ago
Solved

Top 80% Engagement

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...
Need Help
  • vanessafvg's avatar
    vanessafvg
    9 years ago

    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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

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

 

GIO200011's avatar
GIO200011
Advocate I
8 years ago

 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