Another rankx with slicer question

[mmonca]

Hello,

So I have a dataset as

Date	Region	Facility	Unit	Amount	Rank	Year	Quarter	Month	Week Ending	DoW
11 21 16	West	1221	1	3	2	2016	4	11	11 26 16	Mon
11 21 16	West	1221	2	4	3	2016	4	11	11 26 16	Mon
11 21 16	East	3773	1	2	1	2016	4	11	11 26 16	Mon
11 21 16	East	3773	2	7	4	2016	4	11	11 26 16	Mon
11 21 16	East	4554	1	12	6	2016	4	11	11 26 16	Mon
11 21 16	East	4554	2	8	5	2016	4	11	11 26 16	Mon
11 22 16	West	1221	1	7	4	2016	4	11	11 26 16	Tue
11 22 16	West	1221	2	6	3	2016	4	11	11 26 16	Tue
11 22 16	East	3773	1	4	2	2016	4	11	11 26 16	Tue
11 22 16	East	3773	2	8	5	2016	4	11	11 26 16	Tue
11 22 16	East	4554	1	33	6	2016	4	11	11 26 16	Tue
11 22 16	East	4554	2	2	1	2016	4	11	11 26 16	Tue
11 23 16	West	1221	1	1	1	2016	4	11	11 26 16	Wed
11 23 16	West	1221	2	3	2	2016	4	11	11 26 16	Wed
11 23 16	East	3773	1	7	5	2016	4	11	11 26 16	Wed
11 23 16	East	3773	2	5	4	2016	4	11	11 26 16	Wed
11 23 16	East	4554	1	4	3	2016	4	11	11 26 16	Wed
11 23 16	East	4554	2	8	6	2016	4	11	11 26 16	Wed

The goal is to rank each business unit each day. The full dataset has about 20 units across multiple regions and facilites with several years of daily data (so ~20 x ~250 x 4 = ~20,000 rows). The amount value is a calculated column comparing plan to actual. So by ranking we are looking for the unit that was closet to plan each day. I want to have a pivot table that is the following:

						West		East
						1221		3773		4554
Year	Quarter	Month	Week Ending	Date	DoW	1	2	1	2	1	2
2016	4	11	11 26 16	11 21 16	Mon	2	3	1	4	6	5

This pivot table allows the user to expand/collapse any of the column or row headings. The ranking value will be shown as an average, giving, say weekly or monthly average daily rank for each unit. I will be adding slicers to the report allowing the users to filter date ranges and I can already imagine that some users will not want to see all units. I can't anticipate all the different combination of units users may see (i.e., all # 2 units from east, east+central, central, all # 3 units, etc, etc). I need the ranking to be updated so that when certain units or dates are sliced out the ranking doesn't take those units into consideration.

In an Excel spreadsheet this is straightforward. I would have slicers on a table and two calculated columns:

Aggregate = AGGREGATE(3,5,[@[Date]])

Rank = COUNTIFS([Date],[@[Date]],[Amount],"<"&[@Amount],[Aggregate],1)+1

The Aggregate column checks if the row is hidden/sliced/filtered and the rank has three conditions including the check if the row is visible. However, I would prefer to take advantage of PowerPivot's query and compression capabilities.

So this is what I have so far:

(calculated coumn) Overall rank= RANKX(Table1,[Amount],[Amount],1,Dense)

(measure) OverallRankVisible:=RANKX(ALLSELECTED(Table1),Table1[Amount],AVERAGE(Table1[Amount]),1,Dense)

(calculated column) Rank Each Day = RANKX(Table1,CALCULATE(SUM(Table1[Amount]),ALLEXCEPT(Table1,Table1[Date])),,1,Dense)

(calculated column) Daily Unit Rank1 =RANKX(FILTER(Table1,table1[Date]=earlier([Date])),Table1[Amount],[Amount],1,Dense)

(calculated column) Daily Unit Rank2 = CALCULATE(COUNTROWS(Table1)+1,FILTER(Table1,Table1[Date]=EARLIER(Table1[Date])&&Table1[Amount]<EARLIER(Table1[Amount])))

The overall rank does not consider the date. Rank each day column is an overall rank of the day instead of the units within the day. The measure is the only calculation affected by a slicer but it is an overal rank not considering each day. The two Daily Unit Rank columns produce the desired result but is not affected by slicing. I understand that the FILTER function is replacing the filter context created by the slicer. I'm not sure how to focus the count to produce the correct rank given the user selected filter context.

Thank you,

--mm

[v-huizhn-msft]

Hi @mmonca，

For the pivot table you wanted, what do you mean of the last three part of results? For example, the highlighted in red line, the first row(Year Quarter Month Week Ending Date) show the column name, how do you get the 1,2 value?  What do you mean the 2,1 value(highlighted in yellow)? The 21221, 3772, 4554 are form  Facility column, it should be displayed in same column.




>>I need the ranking to be updated so that when certain units or dates are sliced out the ranking doesn't take those units into consideration.

You want to select the Aggerate, Rank in Slicer, the rank will be update after the filter when you select value in slicer, right? The slicer will filter the rows in table. If you rank all the rows using measure, the rank will not be updated when click slicer.

Best Regards,
Angelia

[mmonca]

Hi Angelia

Thanks for responding. So first off sorry for not being clear but that first row of 1 2  1  2  1  2 are the business units. The sample file had two units for each facility. Underneath that heading there is the sample for 11 26 16 with the rankings for the units being, in order, 2 3 1 4 6 5.

If I understand your question right, yes, I would like the ranking to be affected by the slicer. For instance if a unit is rank #1 on some day and then that unit is filtered out then the previous #2 should then become #1 and so on.

Also, I noticed that with a similar sumx measure I can generate a ranking by going to the pivot table options and show value as ranking smallest to largest. While this is a step in the right direction, what would be more helpful would be if the totals and rolled up amounts are not rankings. Absolute rankings should be at the daily level. At higher levels it would be best to have a average value. For instance, if 3 units have a cumulative total ranking score after a week 6, 10, & 14 then the week figure should be 1.2, 2, 2.8 and not 1,2,3. I think that having the average provides more details and is more useful (more challenging too,).

Thanks again everyone.