Row number in calculated table

[gtamir]

Hi, How can I add row number in the Calculated table?

RANKX gives me the same number if the totals are the same.

[v-frfei-msft]

Hi @gtamir ,

Here we go.

running total = 
CALCULATE (
    SUM ( RunningTotalByMetupalim[TOTAL] ),
    FILTER (
        RunningTotalByMetupalim,
        RunningTotalByMetupalim[Metupal Name]
            <= EARLIER ( RunningTotalByMetupalim[Metupal Name] )
    )
)

index_ = 
RANKX (
    RunningTotalByMetupalim,
    RunningTotalByMetupalim[running total],
    ,
    ASC,
    DENSE
)

[gtamir]

Thanks @v-frfei-msft  but I don't understand the RunningTotal.

In my formula, I sort TOTAL first, and then Cumulative Total aggregates the totals. In your Running total it is not.

You sort the table by the name.

 

[v-frfei-msft]

@gtamir ,

What is your excepted result?

[gtamir]

@v-frfei-msft A series of numbers from 1 to the end. If I refer to TOTAL, If some values are the same, I want the index to go forward and not to rank them the same.  

[az38]

hi @gtamir 

try

IndexColumn = CALCULATE(
COUNT('Table1'[Column1]);
FILTER(ALLSELECTED('Table1');'Table1'[Column1]<= MAX('Table1'[Column1]))
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[gtamir]

@az38  thanks for the quick answer. What should be Table1? It does not allow me to enter the current table.

[az38]

@gtamir 

Why?

after creating your calculated table to data model it appears in the right Fields Pane. the you can create a new measure in this table. So, Table1 is your calculated table, Column1 is a field that will determine index order

alternatively, you can use ADDCOLUMNS function over your statement that defines your calculated table. we will try to help you, if you share your table formula here

do not hesitate to give a kudo to useful posts and mark solutions as solution

[gtamir]

The first suggestion is a measure so I can use it in a visual.

Here is the formula and the table.

[az38]

@gtamir 

I see Index field. couldn't it be used as row number?

try to add a measure into you table 

IndexColumn = CALCULATE(
COUNT('RunningTotalByMetupalim'[Total]);
FILTER(ALLSELECTED('RunningTotalByMetupalim');'RunningTotalByMetupalim'[Total]<= MAX('RunningTotalByMetupalim'[Total]))
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[gtamir]

The index is RANKX. It gives the same rank if the total is the same. Then the accumulative total is wrong.

What do you mean measure? I need to add a column.

[az38]

well @gtamir measure it's a like column but more complex 🙂

anyway you can try to use column with the same formula as above, but im not sure it will work

do not hesitate to give a kudo to useful posts and mark solutions as solution

[gtamir]

No, I tried that.

[az38]

@gtamir 

change ";" to comma ","

it depends on system localization

do not hesitate to give a kudo to useful posts and mark solutions as solution

[gtamir]

Still says unexpected expression, 

[gtamir]

I changed the other comma but it gives me the same number all over.

[az38]

@gtamir 

create new measure with the formula as above

do not hesitate to give a kudo to useful posts and mark solutions as solution

[gtamir]

OK, What to do with it? 

[az38]

@gtamir 

share your pbix-file, please, for example at https://uploadfiles.io/

do not hesitate to give a kudo to useful posts and mark solutions as solution

[gtamir]

OK I'll cut it and send it later. Thanks

[gtamir]

https://drive.google.com/file/d/1tmi9FjsBuooX5Gl1gkPR0GT6ZxZZhaBV/view?usp=sharing

You can see that row 10 and 11 have the same rank (10).