DAX Formula Help Needed - Summing Measures

[wg]

Hello fellow Power BI Community Members,

I am working on a report that requires the use of multiple measures. The problem is that I am stuck part way through the process, as using some of the measures is proving difficult. Let me explain what I've done and where I'm stuck, and hopefully someone can help me finish this out.

First, my data comes in a wide data format - meaning multiple columns. For each unit of our company, and for each month of the year, there is a value associated. For a given unit, the data will come in with the unit # in column 1, and then list out 12 columns to the right (1 for each month of the year) with a value in each of those columns.

Second, I then pivot that data using the Summarize function to get that data in a narrow data format - meaning I now only have 3 columns: unit #, month, and value. A sample of that pivoted data is in a spreadsheet at this link: Sample File (look on the Detail tab).

The end users are able to select in filters which units to include in the report. A user can select a single unit or multiple units. The PBIX report needs to aggregate the values by month for the selected unit(s), and then it needs to rank them. I created a total column by doing the following DAX formula:  Total = CALCULATE(SUM('Pivoted Data'[Value]), ALLSELECTED('Pivoted Data'[Unit]))

My ranking works fine now using this formula: Rank = IF(HASONEVALUE('Pivoted Data'[Month]),RANKX(ALL('Pivoted Data'[Month]),[Total]))

The problem I have is that I need to be able to get the Average of the Months ranked 1-3 and another Average of the Months ranked 10-12. Those values will then need to be used in other calculations as well. In that sample file referenced above, I show some of what I was trying to do. If you filter any combination of units on the Details tab, it will update the totals on the Summary Tab, Rank those values, Calculate the total of the top 3 months (and the bottom 3 months), and then gets the average.

I can't seem to do this in my DAX, however. I have tried multiple formulas, satarting with the simple of: Top 3 Month Sum = VALUE(IF([Rank] <= 3, CALCULATE(SUM('Pivoted Data'[Value])), 0))

Nothing seems to work for me, but I am assuming that it is just my limited understanding of DAX. Can anyone help me, please? I am desperately in need of assistance. Thank you!

[v-chuncz-msft · Accepted solution]

@wg,

You may try using SUMMARIZE Function inside the SUMX Function.

[wg]

This works like a charm! I didn't know that was a possibility, but it certainly met my needs. Thank you!

Just in case it helps someone else who is running into the same problem, here is the formula I used to get what I needed:

Top 3 Month Summary = SUMX(SUMMARIZE('Pivoted Data','Pivoted Data'[Month]),[Top 3])

Top 3 = VALUE(IF([Rank] <= 3, CALCULATE(SUM('Pivoted Data'[Value])), 0))

The Top 3 measure allowed me to assign a 0 to anything that is not in the Top 3, and then I was able to use the SUMX(SUMMARIZE)) measure to total that up for use in other calculations. It works similarly for the bottom 3 values I needed.