Create Bin based on Measure value

[Anonymous]

Hi Everyone,

I am strucking here to create custom column based on measure, Please help me on this.

Here is my scneario with example, I have category feild and wanted to create calculated bin column based up on Furniture Sales Percentage.

So firstly, i have created Furniture Sales (%) measure by using below expresson, and it is working fine.

Furniture Sales (%) = var x=CALCULATE(SUM(Sheet1[Sales]),FILTER(Sheet1,Sheet1[Category]="Furniture"))
                                  return
                                  x/SUM(Sheet1[Sales])

next step is, I have created another measure to form binning such as like below

Furniture Sales Bin = IF([Furniture Sales (%)]>0 && [Furniture Sales (%)]<=0.30, "0%-30%",
                                     IF([Furniture Sales (%)]>0.30 && [Furniture Sales (%)]<=0.60,"30% - 60%",
                                     IF([Furniture Sales (%)]>0.60 && [Furniture Sales (%)]<=0.80,"60% - 80%",
                                     IF([Furniture Sales (%)]>0.80,">80%" ))))

and this range measure also working fine, but problem is that i couldn't able to use this range measure in Legends.

Hence i wanted to create custom column similar to this logic. 

Looking forward for your responce.

Thanks,

Ganesh

[Lomanaho]

Create the Furniture Sales (%) Measure:
Furniture Sales (%) =
VAR x = CALCULATE(SUM(Sheet1[Sales]), FILTER(Sheet1, Sheet1[Category] = "Furniture"))
RETURN x / SUM(Sheet1[Sales])

Create a Calculated Column for Binning: Instead of using a measure, create a calculated column in your data model. Here’s how you can do it:
Furniture Sales Bin Column =
VAR FurnitureSalesPercentage =
CALCULATE(
SUM(Sheet1[Sales]),
FILTER(Sheet1, Sheet1[Category] = "Furniture")
) / SUM(Sheet1[Sales])
RETURN
IF(FurnitureSalesPercentage > 0 && FurnitureSalesPercentage <= 0.30, "0%-30%",
IF(FurnitureSalesPercentage > 0.30 && FurnitureSalesPercentage <= 0.60, "30%-60%",
IF(FurnitureSalesPercentage > 0.60 && FurnitureSalesPercentage <= 0.80, "60%-80%",
IF(FurnitureSalesPercentage > 0.80, ">80%", "Other"))))

1. Use the Calculated Column in Visuals: Now that you have the calculated column, you can use it in your visuals, including as a Legend. This approach ensures that the binning logic is applied at the row level, allowing it to be used across different visualizations.

I have implemented a similar approach for analyzing sales data in a furniture store https://cabinetsbay.com/, and it worked effectively in visualizing different sales categories.

[v-juanli-msft]

Hi @Anonymous

I'm not sure I understand how the calculated bin column based up on Furniture Sales Percentage should be calculated.

I test by applying your Furniture Sales Bin measure in a calculated column and I am able to work out with the same result as measure.

Additionally, I modify your first measure with the following formula

Measure = DIVIDE(CALCULATE(SUM(Sheet1[Sales]),FILTER(Sheet1,[Category]="a")),CALCULATE(SUM(Sheet1[Sales]),ALL(Sheet1)))

Please let me know if I have misunderstood your problem.

Best Regards

Maggie

[Anonymous]

Hi Maggie,

I have checked your solution.

Caliculated measure given in your post, percentage should not be across all cities but it should be individual city level.

for example:

below table, city, total sale, category are predefined. 

city: 1, total sale: 100/-, category: furniture,  furniture sale: 10%, needed column: 0-30% (as per our requirment)

city: 2, total sale: 20/-, category: furniture, furniture sale: 50%, needed column: 30% - 60%

city: 4, total sale: 40/-, category: furniture, furniture sale: 70%, needed column: 60% - 80% 

.... etc

Furniture sale is calculated as below:

Furniture Sales (%) = var x=CALCULATE(SUM(Sheet1[Sales]),FILTER(Sheet1,Sheet1[Category]="Furniture"))
return
x/SUM(Sheet1[Sales])

now "needed column" is as below

(IF([Furniture Sales (%)]>0 && [Furniture Sales (%)]<=0.30, "0% - 30%",
IF([Furniture Sales (%)]>0.30 && [Furniture Sales (%)]<=0.60,"30% - 60%",
IF([Furniture Sales (%)]>0.60 && [Furniture Sales (%)]<=0.80,"60% - 80%",
IF([Furniture Sales (%)]>0.80,"> 80%" )))))

solution needed for adding "needed column" in pie chart legends.

Thanks,

Ganesh.

[v-juanli-msft]

Hi @Anonymous

Test based on your example.

 

"rate" is  used in a measure, "flag2" is in a calculated column

rate =
IF (
    MAX ( [Category] ) = "a",
    DIVIDE (
        CALCULATE (
            SUM ( Sheet1[Sales] ),
            FILTER ( ALLEXCEPT ( Sheet1, Sheet1[City] ), [Category] = "a" )
        ),
        CALCULATE ( SUM ( Sheet1[Sales] ), ALLEXCEPT ( Sheet1, Sheet1[City] ) )
    ),
    BLANK ()
)

flag2 =
IF (
    [rate] > 0
        && [rate] <= 0.3,
    "0%-30%",
    IF (
        [rate] > 0.3
            && [rate] <= 0.6,
        "30%-60%",
        IF ( [rate] > 0.6 && [rate] <= 0.8, "60%-80%", IF ( [rate] > 0.8, ">80%" ) )
    )
)

Best Regards

Maggie