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.
Trying to create a little (fun) winners podium that can be filled in dynamically and I'm not figuring out how to pull out the top 3 from the table.
So for any span of time, I want to know the top Rep with highest sales per week. Then the top three Weeks.
I want to pull out the Rep Name, Week Start that it happened and the Sales amount for that Rep that Week.
Thoughts?
Solved! Go to Solution.
Hi, @nesselman
You can try steps as below:
1.Add a calculated column 'WeekStart(Mon)' in table 'SalesTable'
WeekStart(Mon) = LOOKUPVALUE(DateTable[WeekStart(Mon)],DateTable[Date],SalesTable[Date])
2.Add a calculated table
New Table = SUMMARIZE(SalesTable,SalesTable[WeekStart(Mon)],RepTable[KnownAs],"sales",SUM(SalesTable[Sales]))
3.Create calculated columns 'rank' and 'value' as below:
Rank = RANKX('New Table','New Table'[sales])
Value = 'New Table'[KnownAs]&" ("&FORMAT('New Table'[WeekStart(Mon)],"d-mmm")&") $"&'New Table'[sales]
4.Drag the field 'value' into the card visual , and drag the field 'rank' into the visual filter pane to filter the data
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you. This was very helpful (and taught me another technique). Now I'm working on filtering that newTable down to use a Year selected by a Slicer. Appreciate your solution.
Thank you. This was very helpful (and taught me another technique). Now I'm working on filtering that newTable down to use a Year selected by a Slicer. Appreciate your solution.
Hi, @nesselman
You can try steps as below:
1.Add a calculated column 'WeekStart(Mon)' in table 'SalesTable'
WeekStart(Mon) = LOOKUPVALUE(DateTable[WeekStart(Mon)],DateTable[Date],SalesTable[Date])
2.Add a calculated table
New Table = SUMMARIZE(SalesTable,SalesTable[WeekStart(Mon)],RepTable[KnownAs],"sales",SUM(SalesTable[Sales]))
3.Create calculated columns 'rank' and 'value' as below:
Rank = RANKX('New Table','New Table'[sales])
Value = 'New Table'[KnownAs]&" ("&FORMAT('New Table'[WeekStart(Mon)],"d-mmm")&") $"&'New Table'[sales]
4.Drag the field 'value' into the card visual , and drag the field 'rank' into the visual filter pane to filter the data
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |