DAX Sum of Average to Rank

Question

I have a pivot table that pulls a rate by person from powerpivot. The rate is a calculated field, not a column and I need the Grand Total to show as a SUM of the rates, not an average. This is purely for ranking purposes to show who has the highest overall rates to lowest. This is what it looks like with the field I want:

Person Jan-2018 Feb-2018 Mar-2018 GrandTotal [DesiredField] A 80% 71% 82% 77.6% 233% B 76% [blank] 85% 80.5% 161% C 82% 85% 79% 82% 246%

So person C would be at the top followed by A then B. Due to OLAP limitations I can't create a calculated field and 'Summarize Values By' field is greyed out. If there is a better work around please let me know.

So you cannot add anything to your PowerPivot model? Maybe you can use the Pivot Table as source for your new table. Create a new sheet and do some lookups, to create the table you want and add the desired calculated column. Edit: you ask (title) for a DAX formula. Why, since you can't create a calculated column? — TJ_
@TJ_ yes, ideally i could use a DAX formula in powerpivot to fix this problem. since the data will refresh regularly and the number of rows change, i think it's best to leave it as a pivot table. the rate i'm pulling is a calculated measure so creating another column in powerpivot doesn't solve the problem. I tried SUM([calculated measure]) but results in error, as SUM only accepts column reference as an argument. — J.1
Ok, clear. Sorry, I missed the difference in Field and Column. Now I understand your problem. Could you describe the data or show the lines, needed to calculate the current [Rate]? Maybe then we can create a Calculated Field which produces the figure you want. — TJ_
@TJ_ the rate is for an entire population, based off of one column and its values are either 1 or 0. rate = sum([column])/count[column]). so a person could have multiple rows over time where [column] = 1 if complete and 0 otherwise. is this enough of a desription? — J.1
Thank you. Yes. Based on your description my answer below seems to work. Hope you'll understand it, and that it will work for your situation too. — TJ_

TJ_ TJ_ · Accepted Answer · 2018-04-02T20:47:05

Since there's no data model given, I assume you have a measure (calculated field), some persons and a Date dimension with a 'Month-Year' column.

You could try the SUMX function, it iterates over a given table of values. In this case, you need to sum up the calculated [Rate], for each 'Month-Year'.

Example:

SumRatePerMonth :=
SUMX(
     VALUES('DimDate'[Year-Month]),
     [Rate]
)

The formula iterates over the [Month-Year] column in the DimDate table*, calculates the [Rate] for each [Month-Year], and sums it.

Note: Like I said, I assume you have a some sort of Date dimension, from which you pull the Month-Year column. If my assumption is wrong, please provide us with a sample or screenshot of your datamodel.

DAX Sum of Average to Rank

1 Answers