Forum Discussion

oren's avatar
oren
Helper III
7 years ago
Solved

Ranking by groups

Hi

 

i what to create a Mesure that rankink rows for groups of values order by number -

same as in sql row_number over (partition by X,Y,Z order by L)

 

is it possible?

 

thank you very mutch

 

oren 

Need Help
  • themistoklis's avatar
    themistoklis
    7 years ago

    oren

     

    Please ignore the previous formula.

    This one works:

     

    Sorting_Rank = 
	RANKX (
        FILTER(
			ALL(
				'Table'[A], 'Table'[B], 'Table'[C]
				),
            'Table'[A] = MAX('Table'[A]) &&
            'Table'[B] = MAX('Table'[B])
            ),
			CALCULATE(SUM('Table'[C])), , ASC, Dense
			)

6 Replies

  • AnkitBI's avatar
    AnkitBI
    Solution Sage
    7 years ago

    Please try like below.

     

    Measure 11 = RANKX(FILTER(all(Table1),Table1[Column1] = max(Table1[Column1]) && Table1[Column2] = max(Table1[Column2])),CALCULATE(sum(Table1[Column3])),,ASC,Skip)

    Regards
    AJ
    Do Like Post if response seems good and Worth liking.
    Do Mark as Solution if response resolved your Issue.

    • oren's avatar
      oren
      Helper III
      7 years ago

      it dosent work- i am getting only "1" ( but maybe i am doing somthing not right)

       

      thanks

       

       

    • AnkitBI's avatar
      AnkitBI
      Solution Sage
      7 years ago

      Never used ISONORAFTER before but will definately check. Thanks for Suggestion.

  • themistoklis's avatar
    themistoklis
    Community Champion
    7 years ago

    oren

     

    Try the following formula:

     

    Sorting Rank = IF (
    HASONEVALUE ( 'Table'[A]), 
    RANKX ( ALL ( 'Table'[A], 'Table'[B] ), SUM('Table'[C]), , ASC, Dense)
)
  • themistoklis's avatar
    themistoklis
    Community Champion
    7 years ago

    oren

     

    Please ignore the previous formula.

    This one works:

     

    Sorting_Rank = 
	RANKX (
        FILTER(
			ALL(
				'Table'[A], 'Table'[B], 'Table'[C]
				),
            'Table'[A] = MAX('Table'[A]) &&
            'Table'[B] = MAX('Table'[B])
            ),
			CALCULATE(SUM('Table'[C])), , ASC, Dense
			)