RANKX not working due to "Single value for column XX in table YY cannot be determined"

Question

I am attempting to insert a column that continuously sums up all values from another column (here values from GrossMargeEUR) beginning with the largest down to the smallest value. In order to accomplish that I found a video that appears to be offering the solution to my problem:

https://www.youtube.com/watch?v=f5k4fD1YJrQ&ab_channel=EnterpriseDNA

However, I am unable to replicate this solution due to RANKX not being applicable in my example. Creating a measure with RANKX triggers the error message

A single value for column 'GrossMargeEUR' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I am uncertain what this message means as there are no values missing in my first column. Why is it not possible to introduce a proper ranking here that can then be used to create a cumulalative sum column? Can anyone help here, please?

Answer 1

The RANKX function expects the following:

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])  

You are passing a column reference instead of an expression, so first create your rank measure like this:

SalesRank = 
    RANKX(ALLSELECTED('Table'[Product]), 
        CALCULATE(SUM('Table'[Sales])) 
        + DIVIDE (
            RANKX (
                ALLSELECTED ( 'Table'[Product] ),
                    CALCULATE ( MIN ( 'Table'[Product] ) ),
                    ,DESC,DENSE
            ),
            100),,DESC)

The + DIVIDE is there to break ties when 2 products have had the same sales.

Once you have the measure above, you can calculate your cumulative total like this:

Cumulative = 
  VAR crank = [SalesRank]
  RETURN SUMX(
      FILTER(
      SUMMARIZE(
          ALLSELECTED('Table'[Product]),
          'Table'[Product],
          "sales", SUM('Table'[TotalSales]),
          "rank", [SalesRank]
      ), [rank] <= crank), [sales])

So you save the rank for that product in a variable, then use the SUMX with a calculated table which contains all the products up to that rank.