Archive

Posts Tagged ‘DAX Rank’

DAX – Rank

In any business, Ranking of business process systems/stages with corresponding metrics is one of the common requirements. Here we will see the DAX – Rank function with a self-explanation of logic

1. Comparison of ranks over time

define 
measure 'Internet Sales'[PrevSales] = 
      calculate(sum('Internet Sales'[Sales Amount]),parallelperiod('Date'[Date], -1, year))
measure 'Date'[Ranks] = RANKX(ALL('Date'[Calendar Year]),SUMX(RELATEDTABLE('Internet Sales'),[Sales Amount]))
measure 'Date'[PrevRanks] = IF(ISBLANK('Internet Sales'[PrevSales]),BLANK(),RANKX(ALL('Date'[Calendar Year]),'Internet Sales'[PrevSales]))
EVALUATE (
SUMMARIZE ('Internet Sales'
			,'Date'[Calendar Year]
			,"PresentYearRank"
			,'Date'[Ranks]
			,"InternetSales"
			,format(sum('Internet Sales'[Sales Amount]),"currency")
			,"PreviousYearRank"
			,'Date'[PrevRanks]
			,"PrevYearInternetSales"
			, format('Internet Sales'[PrevSales],"currency")
           ) 
         
order by 'Date'[Ranks]



2. Rank of single business logic

DEFINE MEASURE'Geography'[Sales] = sum('Internet Sales'[Sales Amount])
measure 'Geography'[CountryRanks] = RANKX(ALL('Geography'[Country Region Name]),SUMX(RELATEDTABLE('Internet Sales'),[Sales Amount]))
EVALUATE (
 addcolumns(		
			values('Geography'[Country Region Name])								
			,"CountryRanks"
			, 'Geography'[CountryRanks]
			,"SalesAmount"
			, format('Geography'[Sales],"currency")
			)
	     )
		order by 'Geography'[CountryRanks]



3. Rank of nested business logic

DEFINE MEASURE'Geography'[Sales] = sum('Internet Sales'[Sales Amount])
measure 'Geography'[CityRanks] = RANKX(ALL('Geography'[City]),SUMX(RELATEDTABLE('Internet Sales'),[Sales Amount]))
EVALUATE (
 addcolumns(		
			GENERATE(
				values('Geography'[Country Region Name])
					, topn(
						  2	
							,  values('Geography'[City])
							, 'Geography'[Sales]
						, 0
						 )						
			    )								
			,"CityRanks"
			, 'Geography'[CityRanks]
			,"SalesAmount"
			, format('Geography'[Sales],"currency")
			)
	     )
		order by 'Geography'[Country Region Name] asc
		, 'Geography'[Sales] desc

Advertisements
Categories: DAX Tags: