Issue of function Dax Ceiling

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

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 🙂

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.