Forum Discussion

Krishna_Mysore's avatar
Krishna_Mysore
Helper II
8 years ago

DAX Rank Measure needs tweaking

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 errorScreenshot of 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

Need Help

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

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

    • Krishna_Mysore's avatar
      Krishna_Mysore
      Helper II
      8 years ago

      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