calculate Index as Calculated Column using DAX

[Anonymous]

calculate Index as Calculated Column

I want to calculate Index as a Calculated Column for three years 2002, 2003, 2004.  These are the steps I am following-

Thanks in advance for all the help.

Step 1- Calculate sales aggregate year-wise for per material.

Sales per material key = SUMX(VALUES('Sheet1 (2)'[Material]),CALCULATE(SUM('Sheet1 (2)'[sales])))

Step 2- Create an index in a way that sorts sales per material key in descending order and creates an indexing column starting from 1.

Index as Measure-

Ranking =

    RANKX (ALL('Sheet1 (2)), 'Sheet1 (2)'[Sales per material col],, DESC )

Index as Calculated Column-

Rank = IF([year] = 2003, [rank], ALLEXCEPT('Sheet1 (2)','Sheet1 (2)'[Sales per material col]))

I want to calculate Index as a Calculated Column for three years 2002, 2003, 2004. Because I want to use it as a Field for one of the visualizations. And, in Step 3, I have to calculate the Cumulative sales in Decreasing Sales using this index.

Cumulative Sales = CALCULATE(

      SUM('Sheet1 (2)'[Sales per material col]),FILTER(ALL('Sheet1 (2)), 'Sheet1 (2)'[Ranking]<=MAX('Sheet1 (2)'[Ranking]))

Thanks in advance for all the help.

[Greg_Deckler]

@Anonymous DAX indexes are not easy but can be done but not a dynamic one in a column:

The Mythical DAX Index - Microsoft Power BI Community

[amitchandak]

@Anonymous , A column can not interact with slicer value. The only measure can

Creating an index is a bit difficult

RANKX (ALL(Sheet1),calculate(Sum(Sheet1[sales])) ,, DESC )

Vote for

Serial No in Visual Table
https://ideas.powerbi.com/ideas/idea/?ideaid=80612ec5-14a9-4929-a2ee-531943fa51d4

[Anonymous]

@Greg_Deckler , @amitchandak Hey Guys Thanks for your responses. I have updated my question. Please have a look. Thanks in advance!

[v-janeyg-msft]

Hi, @Anonymous 

I agree with @Greg_Deckler . I don't know what you updated, but it still seems messy and I can't see the result of what you want. Even if you use measure first, as long as you use it in the calculated column, the final result can't be dynamic.

According to your description, I think you can create a calculated table to show annual sales of each material and their rank. If you can provide some sample data(without privacy) and your desired result, I can try to help you modify the code.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey