Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
ive never done this before but maybe this will work
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
Proud to be a Super User!
ive never done this before but maybe this will work
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
Proud to be a Super User!
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!
Hi,
Will this work for you? Download the PBI file from here.
This is a great idea! Thank you!
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
Hi,
Share a dataset and show the expected result.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |