Issue of function Dax Ceiling

[LeRuyet]

Hi 

We have a CRM dataset from Kaggle for our first Power BI report, which includes a revenue column for opportunities that we want to categorize into three groups: small, medium, and large.

We found an example on Microsoft’s documentation, but unlike their data, our column contains values of 0 and null. We managed to create a calculated ranking column using DAX, but applying the CEILING function causes a circular dependency error.

CEILING(<number>, <significance>)

In Power Query, the Table.AddRankColumn function gives an error, saying it cannot convert the value into a table. Additionally, the option "Add Column" > General > Rank Column, suggested on Data Scientest, is not available.

Could you help me?

Thanks

Yannick

[Anonymous]

Hi @LeRuyet 

Thanks to dk_dk and Jai-Rathinavel for their attention to this thread and their great replies.

Please consider accepting their replies as a solution if it has helped you solve your problem.

You may not have to use the RANK/CEILING function if you are only considering grouping. The following are ways to use conditional columns and DAX for your reference.

Create a conditional column:

Add a conditional column - Power Query | Microsoft Learn

1. Replace null values with 0

2. Create a conditional column to group based on the revenue column

Create a Calculated Column with DAX:

RevenueCategory = 
SWITCH(
    TRUE(),
    ISBLANK([Revenue]) || [Revenue] <= 5000, "Small",
    [Revenue] <= 10000, "Medium",
    "Large"
)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @LeRuyet 

Just a friendly follow-up to this post.

May I ask if your problem has been resolved? If so please consider accepting helpful replies as a solution, it will make it easier for other users experiencing the same problem to find a solution.
If you have any other questions, please feel free to contact me.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jai-Rathinavel]

Hi @LeRuyet ,

You can create a RevenueRank column using the below M statement, I have included nulls and zeros in my dummy data and generated this expression. You can refer the "#Added Rank"  step and include it in your m code. Once rank column is created at Power query level, you can create calculated column at DAX level

let
    Source = Table.FromRecords({
        [CustomerID = 1, Name = "Bob", Revenue = 200],
        [CustomerID = 2, Name = "Jim", Revenue = 0],
        [CustomerID = 3, Name = "Paul", Revenue = 200],
        [CustomerID = 4, Name = "Ram", Revenue = null],
        [CustomerID = 4, Name = "Ringo", Revenue = 500],
        [CustomerID = 4, Name = "Julie", Revenue = null],
        [CustomerID = 4, Name = "Stella", Revenue = 0]
    }),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text},{"CustomerID", type text}, {"Revenue", Int64.Type}}),
    #"Added Rank" = Table.AddRankColumn(#"Changed Type", "RevenueRank", {"Revenue", Order.Descending}, [RankKind = RankKind.Dense])
in
    #"Added Rank"

Did I answer your question ? Please mark my post as a solution

Thanks,

Jai 🙂

[dk_dk]

Hi @LeRuyet 

I am not really following why you would need CEILING and Rank columns, if your goal is to create a category column based on the revenue column.

You should be able to create your category column in Power Query with a conditional column, or with DAX calculated column, where you can specify the criteria for categorisation. If you need any help with doing that, or if there is some specific tutorial you are trying to follow that uses CEILING and ranking, please share a sample of your data (or the pbix file if you can) and instructions you were trying to follow.

Best regards,
Daniel