Top 3 Sales Rep By Week - Extracting the Line Data in a Card (Dynamic Podium)

[nesselman]

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?

[v-easonf-msft · Accepted 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.

[nesselman · Accepted 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.

[v-easonf-msft · Accepted 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.