Cumulative Points based on Rank of Sales Amount

Chthonian — Thu, 19 Dec 2019 20:30:25 GMT

Hello folks,

I have what i hope is a nice simple question, that I am probably over thinking and getting confused with. Hoping somebody out there can help.......actually I am sure somebody here can help 😄.

Scenario/Request: Create a Sales Leaderboard for our sales people, the based on Rank (1-10) assign a points value to each ranked salesperson. The have a running cumulative total of the points won each month. And at the end of the year the person with the most points wins a free holiday 🏝.

My Setup: Nice & Simple, Date Table connected to Sales Date("Orderlines Date"), Sales Table connected to Sales Person Table("Agent Contacts"). With a disconnected lookup table with Rank & Points.

Measure 1: Top 10 = CALCULATE( [Total Sales], TOPN(10, 'Agent Contacts', [Total Sales], DESC))

Measure 2: Ranking = RANKX(ALL('Agent Contacts'), [Total Sales], ,DESC)

Measure 3: Points = CALCULATE(VALUE(LOOKUPVALUE('Ranking Values'[Points],'Ranking Values'[Rank],[Ranking])))

This works a treat when using a slicer to work out each months sales leaderboard as per the below, but not for the life of me can I get my head around building the Dax to run a cumulative total on points.

End result I am looking to achieve would be a table or visual calulating the total points won each month by a sales person and obviously ranking them.

Name	Month	Points
John	Jan	15
John	Feb	4
John	Mar	12
John	Total	31

I have been hunting for a solution and gotten bogged down and confused with summarize and table variables and group by (all a bit above my current proficiency!😥) so I thought I would seek help from the people in the know!

Thanks in advance guys for any guidance or tips!

David

Re: Cumulative Points based on Rank of Sales Amount

Chthonian — Fri, 20 Dec 2019 15:53:42 GMT

Small bump to see if anybody out there may have a suggestion 😏

Re: Cumulative Points based on Rank of Sales Amount

Chthonian — Sun, 12 Jan 2020 21:30:21 GMT

Hi all,

I just wanted to close this down saying that I found a way to calculate this using SUMMARIZE to create a table with each month as a dimension that then allowed me to SUM & RANK and TOPN the Results.

Cheers

topic Re: Cumulative Points based on Rank of Sales Amount in DAX Commands and Tips

Cumulative Points based on Rank of Sales Amount

Re: Cumulative Points based on Rank of Sales Amount

Re: Cumulative Points based on Rank of Sales Amount