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

vanessafvg's avatar
vanessafvg
Community Champion
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

  • qjc171819's avatar
    qjc171819
    Frequent Visitor
    9 years ago

    You must be a genius. 

    Thanks for great help.

    vanessafvg

  • GIO200011's avatar
    GIO200011
    Advocate I
    8 years ago

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