rank measure regardless of dimension rows included in visual

[lauriedata]

Before I post my inquiry, Here is what the data looks like (I cannot insert a link, it is not working) and my desired results

The user chooses the date range and how high in rank they want to see (e.g. top 5 rank, top 10 rank). 

QUESTION:

Pasted below is a report with 2 matrices. I am trying to rank each member (i.e. loyaltyiid) by the number of points earned during the period included in the date slicer date range.

However, I want the lower matrix in the report to maintain that SAME ranking in the rank column as the top matrix, but I want the lower matrix to display the points by each date within the date range without recalculating the rank by each date. However, when I add the date field to the MATRIX VISUAL (second matrix in the pic), the ranking is not correct. It recalculates the ranking based on EACH date rather than on the total for the entire date range.

I tried wrapping the RETURN statement inside a calculate function, and using removefilters for the date, but that doesn't work. It winds up ranking based on the entire data set.

This function works for the top matrix, but not for the lower matrix, which includes date detail. (I tired wrapping the return in a removefilters(points_award_date) but that just ranked across the whole database, so I took it out).

Rank_TopN =

var _TopN = selectedvalue('TopN'[Top N]) /* user chooses rank level to view */

Return

if(HASONEFILTER(v_flt_ci_cs_adjustment_points[loyalty_id]),

switch(true(),rank(all(v_flt_ci_cs_adjustment_points[loyalty_id]),orderby([Ttl Points Earned cs Adj Points],desc))<=_TopN,

rank(all(v_flt_ci_cs_adjustment_points[loyalty_id]),orderby([Ttl Points Earned cs Adj Points],desc))))

[lauriedata]

I tried rewording my inquiry to clarify.  I hope the first visual addition of desired results helps.

[KNP]

Again, not sure why it's not working for you.

This is my result...

Do you get a different result if you remove your 'Ttl Customer Count...' measure from the table?

[lauriedata · Accepted solution]

I realize my explanation was difficult to get through, and your work got me on the right track.  THANK YOU.  However, I needed the rankings to work across the date range selected by the user.  Your solution sums the total across the database, and does not limit the ranking to the date range selected by the user.  (Coincidentally, the totals across all dates matches the total within the dates in your example; that's a product of the limited data I provided). In order to get the ranking to change within the date range based on the total across the entire date range, but also have a second matrix which shows the idividual totals BY EACH DATE  but keeps the ranking based on all dates across the range, I had to create two rankings and two point sums.  

 For the visual that does not include the indivudal dates:

Rank_TopN Points Total =

var _TopN = selectedvalue('TopN'[Top N]) /* user chooses rank level to view */

Return

if(HASONEFILTER(v_flt_ci_cs_adjustment_points[Loyalty ID]),

switch(true(),rank(all(v_flt_ci_cs_adjustment_points[Loyalty ID]),orderby([Points Earned from CS Adjustment],desc))<=_TopN,

rank(all(v_flt_ci_cs_adjustment_points[Loyalty ID]),orderby([Points Earned from CS Adjustment],desc))))

Points Earned from CS Adjustment =

        CALCULATE(

            SUM(v_flt_ci_cs_adjustment_points[points_earned]),

            ALLEXCEPT(

                v_flt_ci_cs_adjustment_points,

                v_flt_ci_cs_adjustment_points[Loyalty ID], v_flt_ci_cs_adjustment_points[points_award_date]

            )

        )

FIRST VISUAL RESULTS:

 For the visual that DOES include the indivudal dates:

Rank_TopN Points =

var _TopN = selectedvalue('TopN'[Top N]) /* user chooses rank level to view */

Return

if(HASONEFILTER(v_flt_ci_cs_adjustment_points[Loyalty ID]),

switch(true(),rank(all(v_flt_ci_cs_adjustment_points[Loyalty ID]),orderby([CS Adjust Points],desc))<=_TopN,

rank(all(v_flt_ci_cs_adjustment_points[Loyalty ID]),orderby([CS Adjust Points],desc))))

CS Adjust Points =

        CALCULATE(

            SUM(v_flt_ci_cs_adjustment_points[points_earned]),

            ALLEXCEPT(

                v_flt_ci_cs_adjustment_points,

                v_flt_ci_cs_adjustment_points[Loyalty ID]

            )

        )

Second Visual (partial) results

[Ashish_Mathur]

Struggling to understand what you want.  First of all, share data in a format that can be pasted in an MS Excel file.  In that file, show the expected result.

[lauriedata]

Hi.  Thank you for responding.  The data I added is realistic.  It has agent and date and points.  There are no relationships.  As I noted, I've tried to add a hyperlink and it does work.  I'm looking into that but don't have time now.  Thank you again for your efforts.  

[KNP]

Hi @lauriedata,

If there's no more complexity in the actual model, I'm not sure why my solutions aren't working for you, because I used your data, and it seems to be producing the expected result.

(It's always a good idea to paste data rather than screenshots. Copy/paste from Excel works fine.)

[KNP]

Unfortunately, we don't have enough information about your model to be sure how to solve it.

If you can paste some more realistic sample data and show your model relationships, it may be easier.

As for the measure, this is actually going to work better for the date selection...

Ttl Points Earned cs Adj Points = 
        CALCULATE(
			SUM(v_flt_ci_cs_adjustment_points[points])
            , ALLSELECTED(v_flt_ci_cs_adjustment_points[agent], v_flt_ci_cs_adjustment_points[awarded_date])
		)

[Shahid12523]

1.Use REMOVEFILTERS([awarded_date]) in your rank calculation so the rank is based only on total points per loyalty_id across the selected date range.

2.This ensures the same rank repeats across all date rows for each loyalty ID.

3.Then sort the matrix by your [Rank_TopN] measure—not by date or loyalty ID.

[lauriedata]

@Shahid12523 Thank you.  Can you please help me with where to add the remove filters?  I've been doing Power bi for months now but am stil unclear on all, removefilters, etc.  I tried adding it but had to switch to rankx and it didn't work.  Can I add it somehow to the rank formula I have above?  And do I need the "if(hasonefilter) " portion ?  Thank you again.

[KNP]

I don't see a problem with your ranking (adjusted the TopN so I didn't have to create)...

Rank_TopN = 
		VAR _TopN = 5 //selectedvalue('TopN'[Top N]) /* user chooses rank level to view */
		RETURN
			IF(
				HASONEFILTER(v_flt_ci_cs_adjustment_points[loyalty_id]),
				SWITCH(
					TRUE(),
					RANK(
						ALLSELECTED(v_flt_ci_cs_adjustment_points[loyalty_id]),
						ORDERBY(
							[Ttl Points Earned cs Adj Points],
							DESC
						)
					) <= _TopN, RANK(
						ALLSELECTED(v_flt_ci_cs_adjustment_points[loyalty_id]),
						ORDERBY(
							[Ttl Points Earned cs Adj Points],
							DESC
						)
					)
				)
			)

Maybe it's your measure?

This is what I used...

Ttl Points Earned cs Adj Points = 
        CALCULATE(
			SUM(v_flt_ci_cs_adjustment_points[points]),
			ALLEXCEPT(
				v_flt_ci_cs_adjustment_points,
				v_flt_ci_cs_adjustment_points[loyalty_id]
			)
		)

As for working with the selected date range, would require further testing.

[lauriedata]

For some reason that did not work for me.  It removed some of the ranks (i.e. only showed ranks 3 through 6 if I chose top N to be 6) and it gave a repeated total by date, rather than the total by each date.  I need the total for each date to show when I add date to the filter, but I need the ranking to be based on teh total across all dates in the range in the slicer (not each date in the visual).