Referencing column with a Ranx

[shansenTrek]

Hi Datanauts,

I am trying to create one table that will show the top performing employees for multiple periods of time like the table below.  It needs to be one table to be able to scroll and see all 50+ locations.

Location	Employee	Yesterday % of Total	Employee	Prior Day % of Total	Employee	Month % of Total
Store 1	Steve	47%	Josh	49%	Steve	48%
Store 2	Royce	60%	Matt	42%	Erik	54%
Store 3	Thomas	88%	Thomas	86%	Thomas	86%
Store 4	George	71%	Wyatt	42%	George	81%
Store 5	Paul	47%	Paul	48%	Paul	53%

My data table is structured as follows.  

Date	Year	Location	Employee	Amount
5/13/2020	2020	Store 1	Steve	447
5/13/2020	2020	Store 2	Royce	281
5/13/2020	2020	Store 3	Thomas	277
5/13/2020	2020	Store 4	George	445
5/13/2020	2020	Store 5	Paul	303
5/14/2020	2020	Store 1	Josh	242
5/14/2020	2020	Store 2	Matt	493
5/14/2020	2020	Store 4	Wyatt	258
5/14/2020	2020	Store 2	Erik	474

I have multiple measures to calculate the % of Total for the different time periods and then using the Rankx to rank employees by store for each time period.  But I cannot figure out the next steps to turn it into the desired table shown above.  Here is what my current table looks like.  

Location	Yesterday Rank	Yesterday % of Total	Prior Day Rank	Prior Day % of Total	Month Rank	Month % of Total
Store 1	2	39%	1	49%	3	21%
Store 1	1	47%	2	25%	1	48%
Store 1	3	14%	3	26%	2	31%
Store 2	1	60%	3	18%	2	25%
Store 2	3	20%	1	42%	3	21%
Store 2	2	20%	2	40%	1	54%
Store 3	1	88%	1	86%	1	86%
Store 3	2	8%	3	5%	2	8%
Store 3	3	4%	2	9%	3	6%

Is there a way to reference the employee name associated with the rankings to replace the time period rankings with the employee?

Thanks!

[v-zhenbw-msft · Accepted solution]

Hi @shansenTrek ,

We can use the following steps to meet your requirement.

1. Create a month column.

Month = MONTH('Table'[Date])

2. We can create six measures and put them to a table visual.

Yesterday E = 
var t = SUMMARIZE(FILTER('Table','Table'[Date] = TODAY()-1),'Table'[Employee],"YesterSales",SUM('Table'[Amount]))
return MAXX(TOPN(1,t,[YesterSales],DESC),[Employee])

Prior E = 
var t = SUMMARIZE(FILTER('Table','Table'[Date] = TODAY()-2),'Table'[Employee],"PSales",SUM('Table'[Amount]))
return MAXX(TOPN(1,t,[PSales],DESC),[Employee])

Month E = 
var t = SUMMARIZE(FILTER('Table','Table'[Month] = MONTH(TODAY())),'Table'[Employee],"MSales",SUM('Table'[Amount]))
return MAXX(TOPN(1,t,[MSales],DESC),[Employee])

Yesterday % of Total = 
var Y = TODAY()-1
return
CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]=Y)) / CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]=Y))

Prior Day % of Total = 
var Y = TODAY()-2
return
CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]=Y)) / CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]=Y))

Month % of total = 
DIVIDE(CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Month]=MONTH(TODAY()))) , CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Month]=MONTH(TODAY()))))

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.       

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft · Accepted solution]

Hi @shansenTrek ,

We can use the following steps to meet your requirement.

1. Create a month column.

Month = MONTH('Table'[Date])

2. We can create six measures and put them to a table visual.

Yesterday E = 
var t = SUMMARIZE(FILTER('Table','Table'[Date] = TODAY()-1),'Table'[Employee],"YesterSales",SUM('Table'[Amount]))
return MAXX(TOPN(1,t,[YesterSales],DESC),[Employee])

Prior E = 
var t = SUMMARIZE(FILTER('Table','Table'[Date] = TODAY()-2),'Table'[Employee],"PSales",SUM('Table'[Amount]))
return MAXX(TOPN(1,t,[PSales],DESC),[Employee])

Month E = 
var t = SUMMARIZE(FILTER('Table','Table'[Month] = MONTH(TODAY())),'Table'[Employee],"MSales",SUM('Table'[Amount]))
return MAXX(TOPN(1,t,[MSales],DESC),[Employee])

Yesterday % of Total = 
var Y = TODAY()-1
return
CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]=Y)) / CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]=Y))

Prior Day % of Total = 
var Y = TODAY()-2
return
CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]=Y)) / CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]=Y))

Month % of total = 
DIVIDE(CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Month]=MONTH(TODAY()))) , CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Month]=MONTH(TODAY()))))

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.       

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shansenTrek]

@v-zhenbw-msft, thank you so much for your help.  The Employee measures work great! Can you please help me tweak the % of total measures?  I want to display the % of the store total instead of the time period total.  The data I provided is not great and I apologize for that but the results for store 2 should look like this and everything else should be 100%.

Location	Yesterday E	Yesterday %	Prior E	Prior %	Month E	Month %
Store 2	Matt	51.0%	Royce	100%	Matt	40%

I tried to change the FILTER(ALL to the employee column but now there is an error saying it is looking for a single value.  I have tried multiple variations but to no avail.  Please help again!

[v-zhenbw-msft · Accepted solution]

Hi @shansenTrek ,

We can create three measures to replace [Yesterday % of Total], [Prior Day % of Total], [Month % of total].

Replace Yesterday % of Total = 
var x = [Yesterday E]
var y = MAX('Table'[Date])-1
return
CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Employee]=x && 'Table'[Date]=y)) / CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]=y))

Replace Prior Day % of Total = 
var x = [Prior E]
var y = MAX('Table'[Date])-2
return
CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Employee]=x && 'Table'[Date]=y)) / CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Date]=y))

Replace Month % of total = 
var x = [Month E]
var y = MONTH(MAX('Table'[Date]))
return
CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Employee]=x && 'Table'[Month] = y)) / CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Month]=y))

The result like this,

You also can replace MAX(Table[date]) to TODAY().

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data?

It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to OneDrive For Business and share the link here.

Please don't contain any Confidential Information or Real data in your reply.

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.