Referencing column with a Ranx

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.

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.