Using RANKX to replicate M like ranking (DAX not M)

[smpa01]

Hello Experts,

Please find attached my raw data - https://drive.google.com/open?id=1OittEltL9lIgIg04q6hh3zHZ-cW6PbuU.

What I am trying to achieve is to create a subset that would give me top 10 overspent and top 10 underpent sites by fiscal year-month-service-site combination.

It is an easy task for me to acheive in M by doing the following. But I can't seem to have DAX do what M does

let
    Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Desktop\RANKX_DAX.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Site #", type text}, {"Service", type text}, {"Fiscal_Year", Int64.Type}, {"Fiscal_Month", Int64.Type}, {"Variance (Accrual-Actual)", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [#"Variance (Accrual-Actual)"] >= 0),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Fiscal_Year", "Fiscal_Month", "Service"}, {{"ad", each _, type table [#"Site #"=text, Service=text, Fiscal_Year=number, Fiscal_Month=number, #"Variance (Accrual-Actual)"=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let 
    Source=[ad],
    Sort=Table.Sort(Source, {{"Variance (Accrual-Actual)", Order.Descending}}),
    Index=Table.AddIndexColumn(Sort,"Index",1,1),
    Filter=Table.SelectRows(Index, each [Index] >= 1 and [Index] <= 10)
in 
    Filter),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Site #", "Service", "Fiscal_Year", "Fiscal_Month", "Variance (Accrual-Actual)", "Index"}, {"Site #", "Service", "Fiscal_Year", "Fiscal_Month", "Variance (Accrual-Actual)", "Index"}),
    Custom1 = Table.SelectRows(#"Changed Type", each [#"Variance (Accrual-Actual)"] < 0),
    Custom2 = Table.Group(Custom1, {"Fiscal_Year", "Fiscal_Month", "Service"}, {{"ad", each _, type table [#"Site #"=text, Service=text, Fiscal_Year=number, Fiscal_Month=number, #"Variance (Accrual-Actual)"=number]}}),
    Custom3 = Table.AddColumn(Custom2, "Custom", each let 
    Source=[ad],
    Sort=Table.Sort(Source, {{"Variance (Accrual-Actual)", Order.Ascending}}),
    Index=Table.AddIndexColumn(Sort,"Index",1,1),
    Filter=Table.SelectRows(Index, each [Index] >= 1 and [Index] <= 10)
in 
    Filter),
    #"Removed Other Columns1" = Table.SelectColumns(Custom3,{"Custom"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Site #", "Service", "Fiscal_Year", "Fiscal_Month", "Variance (Accrual-Actual)", "Index"}, {"Site #", "Service", "Fiscal_Year", "Fiscal_Month", "Variance (Accrual-Actual)", "Index"}),
    Custom4 = #"Expanded Custom"&#"Expanded Custom1"
in
    Custom4

I am trying to learn how can I generate this table in DAX.

I tried the following.

PositiveTbl = CALCULATETABLE('Sheet2','Sheet2'[Variance (Accrual-Actual)]>=0)
SumMeasure = SUM(PositiveTbl[Variance (Accrual-Actual)])
RankMeasure = RANKX(ALL('PositiveTbl'),[SumMeasure])

Result of my DAX code

My plan was to create a positive table and negative as I did in M and apply the ranking (once I figure out) on both the tables and union them so that I have what M gives me. But clearly I could not figure out the ranking by by fiscal year-month-service-site combination.

If someone can please help would be great.

Thank you in advance.

[v-diye-msft · Accepted solution]

Hi @smpa01 ,

Filter the variance greater than 0, then 

Take a try of below measure:

Rank = RANKX(ALLEXCEPT(Sheet2,Sheet2[Fiscal_Month],Sheet2[Fiscal_Year],Sheet2[Service]),CALCULATE(MAX([Variance (Accrual-Actual)])))