Create a Ranking as a Calculated Column

[Anonymous]

Hello PBI users,

I would like to create a calculated column that woud create a ranking as below, (it is a ranking but essentially I would like to show a distinct number based on how many times the customer appeared previously)

Below is the table, Enumerate is my desired result, I would like to count for all instances that we the Paid column is not "Yes".

Customer	Paid?	Enumerate
Lego	Yes	-
Lego	No	1
Lego	No	2
Lego	No	3
Strauss	Yes	-
Strauss	Yes	-
Strauss	No	1
A&S	No	1
A&S	No	2
A&S	No	3
A&S	No	4
A&S	No	5

Thanks in advance!

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could add a custom column in power query.

1.add custom column:

= Table.Group(#"Removed Columns", {"Customer", "Paid?"}, {{"Count", each  
Table.AddIndexColumn( _, "Index", 1, 1, Int64.Type)}})

2.expand it.

3.add condition column.

The final show:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klNz1fSUYpMLQaSukqxOnAhPxBhiCFihCFiDBYJLilKLC0uRjOLkCiSJY4xpQYGRmbB+EWNsIoaYxU1wSpqqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Paid?" = _t, Enumerate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Paid?", type text}, {"Enumerate", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Enumerate"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Customer", "Paid?"}, {{"Count", each  
Table.AddIndexColumn( _, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Index"}, {"Count.Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Count", "Enumerate", each if [#"Paid?"] = "Yes" then "-" else [Count.Index]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Count.Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Enumerate", type text}})
in
    #"Changed Type1"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

@Anonymous 

As I mentioned earlier you should have either a date or an index column. If you don't then you have to add one using power query. Then try the following code ranking by either date or index (whatever numeric unique per customer column you have)

EEnumerate =
IF (
    TableName[Paid?] = "No",
    RANKX (
        CALCULATETABLE (
            TableName,
            ALLEXCEPT ( TableName, TableName[Customer], TableName[Paid?] )
        ),
        TableName[Date]
    )
)

[Anonymous]

Hello Tamerj1, 

I do not have a date table neither an index table.

By Indextable do you mean just to insert a table with 1-1000 for example and do not create a realationship with the main table?

Thank you

[tamerj1]

@Anonymous 

Yes just insrt an index column using power query. It is just one click operation. Then in dax code replace [Date] with [Index]

[tamerj1]

Hi @Anonymous 

do you have a date or index column?