Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
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.
Solved! Go to 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)])))
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)])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |