IF Calculation Based on Ranked Total Not Summarizing Correctly

[paulsnet1986]

I need to rank inventory items by the SUM of their monthly costs, then work out the total of the top 100 inventory items against non-top 100 inventor items in another table. I've created the below DAX formulas to accomplish this.

Rank = RANKX(ALL('Inventory'[ERN]),[Total])

Top100 = IF( [Rank] < 100, [Total],0)

NONTop100 = IF( [Rank] >= 100, SUM('Inventory'[Total]),0)

In the table, these appear to work just fine. However, the total of the Top100 formula comes out to the total of all costs, and the total of the NONTop100 formula comes out to zero.  This result is the same when summarized by quarter in another table. 

 

[parry2k · Accepted solution]

@paulsnet1986 btw, here are the measures:

Top100 = SUMX ( VALUES ( 'Inventory'[ERN]) ), IF( [Rank] <= 100, [Total] ) )

NONTop100 = SUMX ( VALUES ( 'Inventory'[ERN]) ), IF( [Rank] > 100, [Total] ) )
 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[paulsnet1986]

That worked! Thank you so much!

[parry2k]

@paulsnet1986 reason I asked because you cannot have aggregation on a measure:

NONTop100 = IF( [Rank] >= 100, SUM('Inventory'[Total]),0)

[parry2k]

@paulsnet1986 your DAX expressions are confusing:

Rank -> did you added this as a measure or columns?

Total -> Is it a measure or a column?

[paulsnet1986]

Both as measures