DAX Rank Measure needs tweaking

[Krishna_Mysore]

Hi Everybody

I'm trying to Rank on two different columns ie  Arrangers (ie Sales persons) and Product from my Transaction Table based on the filter (passed through slicer for Top 5,10 or 20 Customers) which needs to be dynamic. The formula works if ranking is based on Arrangers however when I bring the product fields under the Arranger visual in matrix table, the ranking fails.

Based on the online resources available, I tried to tweak my formula to incorporate IsFiltered criteria but the its just not working. Below is my formula and the corresponding image of the error

Top Casket ASP by Arranger by month = 
VAR RankingArrangerDimension = VALUES('Trans Data'[Arranger])
VAR RankingProductDimension = VALUES('Trans Data'[Part Description])
VAR IsArrangerfiltered = ISFILTERED('Trans Data'[Arranger])
VAR IsProductfiltered = ISFILTERED('Trans Data'[Part Description])
VAR RankingSelect = [Top Ranking Select]
RETURN IF(IsArrangerfiltered,CALCULATE([Avg Selling Price (Billed) Monthly],FILTER(RankingArrangerDimension,RANKX(ALL('Trans Data'[Arranger]),[Avg Selling Price (Billed) Monthly],,DESC)<=RankingSelect),

IF(IsProductfiltered,CALCULATE([Avg Selling Price (Billed) Monthly],FILTER(RankingProductDimension,RANKX(ALL('Trans Data'[Part Description]),[Avg Selling Price (Billed) Monthly],,DESC)<=RankingSelect)))),Blank())

Error message

[Ashish_Mathur]

Hi,

Share your file and show the problem.  Please also show the expected result there.

[Krishna_Mysore]

Hi Ashish

Below are the snapshots of the visuals.  I'm basically ranking the performance of SalesReps (herein called as Arrangers) based on the Avg Sellng Price of the products they have sold. I have created dynamic measure to calculate Top 5,Top 10 or Top20 Ranking and dynamically displayed for Month or YTD time measure.

The ranking based on ASP by Arrangers works fine as shown below, however  if I add " product category" (herein called "Part Description") under the Arrangers field, the ranking does not show the breakup of ASP (Also shown in the second image).

I need help to fix the measure to dispaly the breakdown of the ASP Ranking when "Product Description is drilled down.

Ranking at Parent Level

 Breakup of the Products does not disiplay correctlyBelow is the link to pbix.

 https://onedrive.live.com/?cid=F909C36B11706829&id=F909C36B11706829%21161&parId=root&o=OneUp

Thanks

[Ashish_Mathur]

Hi,

There is no file there.

[Krishna_Mysore]

https://1drv.ms/u/s!AilocBFrwwn5gSFBiI3ooVh0kxWs

Can you try it again please.

Thanks

[Ashish_Mathur]

Hi,

Take any one Arranger, say Sheridan Walder and please let me know what exact answers are you expecting when Part Description (from the Trans data old table) is dragged in?

[Krishna_Mysore]

[Ashish_Mathur]

Hi,

Those images are very small.  I cannot understand anything clearly there.