How to add total sum of each rows within group with DAX Power BI

Question

I am trying to give rank column of every group which repeating in every rows within the group of the original table but not the shape of after sum-up.

The formula i found in another site but it show an error : https://intellipaat.com/community/9734/rank-categories-by-sum-power-bi

Table1

+-----------+------------+-------+

| product   | date       | sales |

+-----------+------------+-------+

| coffee    | 11/03/2019 | 15    |

| coffee    | 12/03/2019 | 10    |

| coffee    | 13/03/2019 | 28    |

| coffee    | 14/03/2019 | 1     |

| tea       | 11/03/2019 | 5     |

| tea       | 12/03/2019 | 2     |

| tea       | 13/03/2019 | 6     |

| tea       | 14/03/2019 | 7     |

| Chocolate | 11/03/2019 | 30    |

| Chocolate | 11/03/2019 | 4     |

| Chocolate | 11/03/2019 | 15    |

| Chocolate | 11/03/2019 | 10    |

+-----------+------------+-------+

The Goal

+-----------+------------+-------+-----+------+

| product   | date       | sales | sum | rank |

+-----------+------------+-------+-----+------+

| coffee    | 11/03/2019 | 15    | 54  | 5    |

| coffee    | 12/03/2019 | 10    | 54  | 5    |

| coffee    | 13/03/2019 | 28    | 54  | 5    |

| coffee    | 14/03/2019 | 1     | 54  | 5    |

| tea       | 11/03/2019 | 5     | 20  | 9    |

| tea       | 12/03/2019 | 2     | 20  | 9    |

| tea       | 13/03/2019 | 6     | 20  | 9    |

| tea       | 14/03/2019 | 7     | 20  | 9    |

| Chocolate | 11/03/2019 | 30    | 59  | 1    |

| Chocolate | 11/03/2019 | 4     | 59  | 1    |

| Chocolate | 11/03/2019 | 15    | 59  | 1    |

| Chocolate | 11/03/2019 | 10    | 59  | 1    |

+-----------+------------+-------+-----+------+

The script

sum =

SUMX(

    FILTER(

         Table1;

         Table1[product] = EARLIER(Table1[product])

    );

    Table1[sales]

) 

The Error :

EARLIER(Table1[product]) # Parameter is not correct type cannot find name 'product' 

What's wrong with the script above ? * not able to test this script:

rank = RANKX( ALL(Table1); Table1[sum]; ;; "Dense" )

before fixed the sum approach

Solution

The script is designed for a calculated column, not a measure. If you enter it as a measure, EARLIER has no "previous" row context to refer to, and gives you the error.

Create a measure:

Total Sales = SUM(Table1[sales])

This measure will be used to show sales.

Create another measure:

Sales by Product =
SUMX(
  VALUES(Table1[product]);
  CALCULATE([Total Sales]; ALL(Table1[date]))
)

This measure will show sales by product ignoring dates.

Third measure:

Sale Rank = 
  RANKX(
     ALL(Table1[product]; Table1[date]); 
     [Sales by Product];;DESC;Dense)

Create a report with product and dates on a pivot, and drop all 3 measures into it. Result:

Tweak RANKX parameters to change the ranking mode, if necessary.

Comments

[RADO]

Did you enter your script as a calculated column or as a measure?

[rane]

as a measure. And also tried with comma not semicolon

[rane]

The syntax for 'Table1' is incorrect. (DAX(SUMX(VALUES(Table1[product]),CALCULATE(SUM(Table1[sales‌​]))))). Jump out , what's wrong i did ?

[RADO]

Try to use ; instead of ,

[rane]

RADO thankyou for your answer but the DAX doesn't show the repeating total of rows of each group in the shape of table which showing the "Date" column= "sub-group"

[RADO]

@rane - I modified the answer. Let me know if it works for you.