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,
I have searched relatively hard for this - sorry if it's been covered already - any tips would be helpful.
I have a series of 34,000 transaction data for various timesheet entries. I have been using tables and visual level filters to provide metrics that show the sum that each person has billed year to date. I have been doing this via a:
Inv. = CALCULATE(sum(Transactions[Value]), Transactions[IsInvoiced] = TRUE) as a measure.
I would like to show on a graph the top 5 people in the group. I have tried to understand RANKX but am struggling.
I've tried: RANKX(ALLSELECTED(Transactions),Transactions[Inv.]) but all I get are 1's... I then found out that was wrong, so have tried:
RANKX(ALLSELECTED(Transactions),CALCULATE(SUM(Transactions[Value]))) ....Esentially trying to cut out the measure part - this results in the same.
RANKX(ALL(Transactions),CALCULATE(SUM(Transactions[Value]))) ...shows some form of ranking but mainly random non-sensical numbers.
Am on the right track with RANKX - there is a TOPN function but it seems to return tables which doesn't really help me.
I don't care that the ranking is in the table visualisation as I want to apply it as a visual filter onto the column graph.
Regards,
Warren
Solved! Go to Solution.
@warrenpoh : I would suggest to calculate TotalSales as
CALCULATE(SUM(Rank_Table[Sales]),Rank_Table[IsSold]="T",ALLEXCEPT(Rank_Table,Rank_Table[Staff Member ])) , add additional filter for StaffMember.
RankSales mentioned by @PavelR works well. Thanks
In this scenario, since your rank is based on each staff's sales total, you should use ALLEXCEPT() to calculate the total group on each staff as @Anonymous suggested.
Rank = RANKX(ALL(Table1[Name]),CALCULATE(SUM(Table1[Sales]),Table1[IsSold]="T",ALLEXCEPT(Table1,Table1[Name])))
Regards,
If you are ranking on people, then Transaction[People ID] has to be wrapped inside your all criteria and not the whole table
e.g. RANKX(ALL(Transactions[People ID]),CALCULATE(SUM(Transactions[Value])))
@lalthan your method seems to work when the raw data is in the table, e.g.:
Staff Member Sales Measure
Warren 32 3
John 52 1
Dave 45 2
But I have data that looks like this: @Anonymous is this ok to assist with?
Staff Member Date Sales IsSold
Warren 24/08/2016 12 T
John 23/08/2016 24 T
Dave 23/08/2016 10 T
Warren 25/08/2016 10 T
John 26/07/2016 15 T
Dave 27/07/2016 11 F
I want to display a table that shows the following. Note that Total Sales is a measure = CALCULATE(SUM(Table[SALES]), Table[IsSold] = TRUE)
Staff Member Total Sales Sales Rank
Warren 22 2
John 39 1
Dave 10 3
I hope tihs explains my issue more clearly. I feel like I'm almost there but not quite.
In this scenario, since your rank is based on each staff's sales total, you should use ALLEXCEPT() to calculate the total group on each staff as @Anonymous suggested.
Rank = RANKX(ALL(Table1[Name]),CALCULATE(SUM(Table1[Sales]),Table1[IsSold]="T",ALLEXCEPT(Table1,Table1[Name])))
Regards,
Hi @warrenpoh. Just use these two DAX calculations:
TOTALSALES = CALCULATE(SUM(TABLE[Sales]);TABLE[IsSold]="T")
RANKSALES = RANKX(ALL(TABLE[Staff Member]);[TOTALSALES])
Regards.
Pavel
@warrenpoh : I would suggest to calculate TotalSales as
CALCULATE(SUM(Rank_Table[Sales]),Rank_Table[IsSold]="T",ALLEXCEPT(Rank_Table,Rank_Table[Staff Member ])) , add additional filter for StaffMember.
RankSales mentioned by @PavelR works well. Thanks
@warrenpoh : Can you share the sample data , with the required result in additional column . Thanks
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |