Forum Discussion

hemann's avatar
hemann
Icon for Helper I rankHelper I
3 years ago
Solved

Countrows based on RANKX result

Hi PBI gurus, 

I have one table of data called Table with two columns "Product" and "Volume". I have performed top 5 Rankx calculation which works fine. I now want to countrows based on Rankx result and return either top 5 or top 4 depending on the number of results from Top5 Measure. For example the Top5 measure result returns 9 results, I want to put a condition if it returns <=5 results I want to return Top5 measure, if not I want return Top4 so <=4. 

I believe this would need a countrows added to the Rankx calculation but struggling to combine the two. 

Top 5 Measure; 

Top5 = if(RANKX(ALL('Table'[Product]),calculate(sum('Table'[Volume])),,DESC,Dense)<=5,RANKX(ALL('Table'[Product]),calculate(sum('Table'[Volume])),,DESC,Dense),blank())
Result from Top 5 Measure
ProductTop5
J1
N2
G3
I3
E4
A5
B5
K5
M5

Result wanted as Top 5 return 9 results (revert to top4)

ProductTop5
J1
N2
G3
I3
E4

Data Table 

Product

Volume
A2
B2
C1
D1
E3
F1
G4
H1
I4
J21
K2
L1
M2
N17
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    hemann 

    you can try this

    Measure 2 = 
VAR _tbl=SUMMARIZE(ALL('Table'),'Table'[Product],"value",sum('Table'[Volume]))
VAR _tbl2=ADDCOLUMNS(_tbl,"rank",rankx(_tbl,[value],,DESC,Dense))
VAR _count=COUNTROWS(FILTER(_tbl2,[rank]<=5))
return If(_count>5,IF([Measure]<5,1))

     

    and add measure 2 to filter 

    pls see the attachment below

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    3 years ago

    hemann 

    here is a workaround for you

    Measure = rankx(all('Table'[Product]),CALCULATE(sum('Table'[Volume])),,DESC)+rankx(all('Table'[Product]),CALCULATE(max('Table'[Product])),,DESC)/100

Measure 2 = rankx(all('Table'[Product]),[Measure],,ASC)

    • hemann's avatar
      hemann
      Icon for Helper I rankHelper I
      3 years ago

      Thanks for your reply. Based on my requirements I'm not after a tie break splitter, but rather logic if top 5 results in more than 5 results revert to top 4. Only way I could see to do this is by countrows logic on the Rankx 

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        3 years ago

        hemann 

        you can try this

        Measure 2 = 
VAR _tbl=SUMMARIZE(ALL('Table'),'Table'[Product],"value",sum('Table'[Volume]))
VAR _tbl2=ADDCOLUMNS(_tbl,"rank",rankx(_tbl,[value],,DESC,Dense))
VAR _count=COUNTROWS(FILTER(_tbl2,[rank]<=5))
return If(_count>5,IF([Measure]<5,1))

         

        and add measure 2 to filter 

        pls see the attachment below