Forum Discussion
Helper ITop 3 Sales Rep By Week - Extracting the Line Data in a Card (Dynamic Podium)
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?
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.
2 Replies
- v-easonf-msft
Community Support
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. 
nesselmanThank 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.