cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

The original table:

C18A`XOIPTVTK(BE]RJB0NP.png

 

What i want to create:

Q[(7_K{{Q}H2TUR[{O3Y1MV.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
Highlighted
Super User I
Super User I

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

You must be a genius. 

Thanks for great help.

@vanessafvg

Highlighted

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!

Highlighted

Hi,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

 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

Highlighted

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

Highlighted

Hi,

 

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

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

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors