Using DAX RANKX to rank Aggregated totals (Newb)

[I_Like_Pi]

I hope I've found the right forum, if this is the wrong place for this and you know of a place to go for such please reply with that. I'm a total newb to this and cant find any decent forums for DAX/POWER BI.

I am trying to rank the results of a pivot in power BI.

I was attempting to follow the wise owl's youtube tutorial. And while I have discovered the sublet difference I cannot figure out how to resolve it. Any help would be appreciated.

I have a single table that I have pivoted to sum the C_Gross of every C_Address. The source is a single table where C_Address is found multiple times, hence the sum.

I would like to rank the sums of C_Gross grouped by C_Address, I have the following measures:

RankGross = RANKX(ALL('Hours_Sum'),'Hours_Sum'[TotalGross])

TotalGross = SUM([C_Gross])

When I use the RankGross measure in the pivot, the rank calculated compares the totaled amount to what would be the ranking if these totals were in the orginal population.

After banging my head for days I realized that in wise Owls tutorial he was able to leverage a relationship in a rationalized DB to get the groupings. Where I have gone All('Hours_Sum') i need the subset of all distint values of C_Address. As I dont have the benefit of a joined table with the subset I am persueing a calulated table.

I first tried,

  RANKX(VALUES('Hours'[C_Address]),'Hours_Sum'[TotalGross])

     - this ends up returning 1's accross the board as it compares to itself.

  RANKX(ALL(VALUES('Hours'[C_Address])),'Hours_Sum'[TotalGross])

    - ALL apparent doesn't like the insertion of the VALUES function, I wasn't expecting that.

I'm still lost. (I last felt this in the presences of REGEX)

[I_Like_Pi]

I tried the Aggregated totals but did not get the correct result. That may be more related to my use in the RANKX formula.

I have 4 weeks worth of gross payments each month.

I would like to create a list of only the top 10 indivuduals for the month.

The 3 columns in the data view are:

C_Address       WK      C_Gross

On the Reports View I want to create a table with only the top 10

Rank_Gross      C_Address     SumofC_Gross

What measures should I create to generate this report pane?

[I_Like_Pi]

It's a payroll table over a period of time. The address is an employee number. I summarized by employee number in a dashboard pane i would like the rank of the summarized monthly totals being calculated. I want the rank so i can filter the top ten results.

MainTable:

C_Address    Week   Hours    Rate  C_Gross

1234      Wk1      40    10      400

1234      Wk2      32     11     352

4321      Wk1      40    15     600

4321     Wk1       15    30     450

4321     Wk2     10     15     150

...

Summary table in dashboard that I would like to include the rank of the total value

C_Address    C_Gross   RankGross

1234     752      2

4321     1200     1

...

My example above has only 2 items. My data has 1000's I would like to view only the top 10 hence I am trying to use RankX. The ranks I show in the example above is what I would like to get. Currently, I am getting the rank as compared to the original individual values, so I dont get to rank 2 until the total falls below 600 in this example. Aternatively I get ranks as compared to the value itself producing a 1 for every line. 

Hopefully that is enough Info.

I will have to wait now until next Tuesday to test your other suggestions. I am doing this on the side to learn how to better present some of my other analytics work. I had to do other work after I posted my query. Thanks for your responses.

[tanckc]

Did you ever get an answer? I have the same issue

[jahida]

Wasn't involved in the initial discussion but this looked fun so I gave it a stab, here's the formula I used:

Rank = SELECTCOLUMNS(FILTER(SELECTCOLUMNS(
					SUMMARIZE(ALL(Table1), Table1[C_Address], "C_Gross", SUM(Table1[C_Gross])),
					"Address", Table1[C_Address], "Rank",
											RANKX(SUMMARIZE(ALL(Table1), Table1[C_Address], "C_Gross", SUM(Table1[C_Gross])), [C_Gross])),
					[Address] = Table1[C_Address]), "r", [Rank])

Very messy but it did give the expected result (I think):

[GilesWalker]

Are you able to provide an example of the data?

@ALeef response sounds about right except you could use SUMX(SUMMARIZE(...)) This creates a distinct table (C_address) and then calculates all the Hours_SUM for each distinct value (C_Address).

Going off of your information the formula would look like this - Aggregated totals = SUMX(SUMMARIZE(Table Name,[C_Adress],[C_Gross]),[C_Gross])

Change Table Name to the name of your table.

Giles

[ALeef]

Can I get a little more explanation on what you are looking for?  To me it sounds like a service business - IE House calls for fixing air conditioners or something, and you are looking to take a list of all the visits, sum them for total hours spent there, and then order them based on those hours.  Is that correct?

If so, I'd use a caclulated table that pulls DISTINCT addresses in, and then a SUMX of hours for that address.  I'm not at my work machine so I can't test it out, sorry.