how can I insert fixed integer in series?

[MarieJ]

Hi, I am very new to Power BI, and have lots of questions.

Is it possible to insert fixed integer in series like {1..27} above red rectangle.

Either Quantity or Sales Amt would be sorted, the rank would be fixed.

I tried to make a new table with {1..27} and insert the column, but it looks like 27 same data of each line.

[Vvelarde]

You can create A Rank

This is for Quantity

Rank=Rankx(all(NameTable);Calculate(Sum(NameTable[Quantity]));;Asc)

This is for Sales Amount

Rank=Rankx(all(NameTable);Calculate(Sum(NameTable[Sales Amount]));;Asc)

[MarieJ]

thank you so much Vvelarde!!

but this is not what I wanted, what I want is not Rank for each column (Qty, Amt), but 1 column of 1 ~ 27 fixed integer, regardless of Qty rank or Amt rank.

In Excel, it's just simple, but I don't know how to do it i Power BI.

[Sean]

@MarieJ So lets assume that the column with the 27 items is Category

If you create a new Summary Table like this... where [Quantity] and [Sales Amount] are both Measures!

Summary Table =
SUMMARIZE (
    TableName,
    TableName[Category],
    "Total Quantity", [Quantity],
    "Total Sales", [Sales Amount]
)

You can then add this DAX Index Column that will basically rank the Category Column A-Z not the Measures!

DAX Index Column (Rank Category A-Z) = 
RANKX ( ALL('Summary Table'), 'Summary Table'[Category],,ASC )

The Index will not change unless you add new categories!

Hope this helps!

[MarieJ]

Oh my God, Thank you so much Sean!

I will try to use this, but I'm not used to use DAX, it takes a while! ^_^

[Vvelarde]

@MarieJ

I'm not sure is that is what you want but :

Go to Edit Query-Add Column-Add Index Column

[MarieJ]

Dear Victor,

Thank you so much for your reply.

I tried add index, but it didn't work..

The reason why I want to have fixed integer is like below,

Rank is fixed, and Sales name, Quantity, Amount can be changed,

 so I can see who is No.1 for Quantiy, who is No.1 for Amount with one fixed column.

is this truely impossible? T.T

[v-micsh-msft]

Hi MarieJ,

If you would like to add a fixed column with the sorting order, which would automatically sort the columns added in the same table visual, I am afraid this is not available.

Visuals in Power BI would gain the column data from the corresponding data table, if all the column shows the MAX value in the same visual, it would require the actual data table has the same structure in the visuals.

We could use the sorting (click on the top of each column, there would be a black triangle showing out, which woud automatically sorting the column) in the visuals to show the MAX value of each column, while this would have effect on the other columns.

For ranking with special column, please follow what Victor suggested.

If you have any further questions, please feel free to post back.

Regards

[MarieJ]

Thank you for your detailed explanation, Michael!

yeah, I can see now the visual i Power BI is quite different to excel.

maybe I have to find the other solution!

[Vvelarde]

@MarieJ

My last solution, is very simple but work in some scenarios

1. Create a Table  with 1 column from 1....27 values

Put in a table Visual

2. In A different Table Visual put the other columns., Try to look like a single table.

[MarieJ]

wow, it looks wonderful!!!

this is truely simple and good!!

Thank you sooooo much Victor!! ^__^

But if the list is over 43, I should scroll down rank table, and sale table seperately ^^;;;

and if the list is a lot, it's quite difficult to scroll down with same position T.T

[Vvelarde]

Do you want a fixed integer? or a Rank of a column?

[MarieJ]

oh! Thank you Vvelarde!

I want a fixed integer which is not changed by either Quanty rank or Sales Amt rank.