cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krishna_Mysore Regular Visitor
Regular Visitor

Dynamic Ranking Issue

Hi Everybody

 

I have created two Ranking measure ,

1) To give Monthly  Avg Selling Price (ASP) Ranking  by Seller (herein called as Arranger) either for Top 5, Top 10, Top 20 (Selection made through slicers)

2) To give YTD ASP Ranking by Seller (Similar to above)

 

These two measures are included in a final measure called "Highest ASP by Arranger"  which will reflect month or YTD ranking based on the slicer selection .

The monthly ranking is working fine , howerver when I toggle to YTD, it does not . It just shows only 2 Arrangers( Salesperson) even though when Top 5 are selected. There are more than 2 Arrangers .

 

Below are the snapshot  and the measures. Due to sensitivity of Data, I'm unable to share the actual PBIX.

 

I presume there must somethig going wrong while filtering the table when YTD calculations are worked out. 

Need help to crack the issue.

 

Thanks

Krishna

 

Top Coffin ASP by Arranger by month =

Top Coffin ASP by Arranger by month = 
VAR RankingDimension = VALUES('Trans Data'[Arranger])
VAR RankingSelect = [Top Ranking Select]
RETURN CALCULATE([Avg Selling Price (Billed) Monthly],FILTER(RankingDimension,RANKX(ALL('Trans Data'[Arranger]),[Avg Selling Price (Billed) Monthly],,DESC)<=RankingSelect)

 

Top Coffin ASP by Arranger YTD = 
VAR RankingDimension = VALUES('Trans Data'[Arranger])
VAR RankingSelect = [Top Ranking Select]
RETURN CALCULATE([Avg Selling Price (Billed) YTD],FILTER(RankingDimension,RANKX(ALL('Trans Data'[Arranger]),[Avg Selling Price (Billed) YTD],,DESC,Dense)<=RankingSelect))
Highest Coffin ASP by Arranger = IF(HASONEVALUE(View[Cal ID]),SWITCH([Calculation ID],1,[Top Coffin ASP by Arranger by month],2,[Top Coffin ASP by Arranger YTD]))

Monthly ASP View.PNGYTD  ASP View.PNG

3 REPLIES 3
Community Support Team
Community Support Team

Re: Dynamic Ranking Issue

Hi Krishna_Mysore,

 

Based on your description, [Avg Selling Price (Billed) Monthly] and [Avg Selling Price (Billed) YTD] are measures you have created, right? Are they based on a same column?

 

And also please check if there’s something wrong in measure [Avg Selling Price (Billed) YTD].

 

Regards,

Jimmy Tao

Krishna_Mysore Regular Visitor
Regular Visitor

Re: Dynamic Ranking Issue

Hi Jimmy

 

On further digging I understand the measure is not displaying if an "Arrangerr" does not have Sales in the current month.

To illustrate this , below is the screen dump . "Rosemary Harris" the first record in the table has YTD sales but no sales in the month of March .  The measure "Highest Coffin ASP by Arranger" does not display the Value though there are are YTD sales associated with this Arranger.  You can evidence in the table, that  she has Sales Value of $33789 and the Avg Selling Price is $3754.  PBIDesktop_2018-04-18_14-56-41.png

 

 

Maybe the syntax relating to filter is not right.

 

Any help much appreciated?

Krishna_Mysore Regular Visitor
Regular Visitor

Re: Dynamic Ranking Issue

This is overdue for me to reslove. Anybody out there who can help me?

Thanks