Create Bin based on Measure value

Question

Hi Everyone,&nbsp;I am strucking here to create custom column based on measure, Please help me on this.&nbsp;Here is my scneario with example, I have category feild and wanted to create calculated bin column based up on&nbsp;Furniture Sales Percentage.&nbsp;So firstly, i have created Furniture Sales (%) measure by using below expresson, and it is working fine.&nbsp;Furniture Sales (%) = var x=CALCULATE(SUM(Sheet1[Sales]),FILTER(Sheet1,Sheet1[Category]="Furniture"))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x/SUM(Sheet1[Sales])&nbsp;next step is, I have created another measure to form binning such as like belowFurniture Sales Bin = IF([Furniture Sales (%)]&gt;0 &amp;&amp; [Furniture Sales (%)]&lt;=0.30, "0%-30%",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF([Furniture Sales (%)]&gt;0.30 &amp;&amp; [Furniture Sales (%)]&lt;=0.60,"30% - 60%",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF([Furniture Sales (%)]&gt;0.60 &amp;&amp; [Furniture Sales (%)]&lt;=0.80,"60% - 80%",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IF([Furniture Sales (%)]&gt;0.80,"&gt;80%" ))))&nbsp;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.&nbsp;&nbsp;&nbsp;Looking forward for your responce.&nbsp;Thanks,Ganesh

v-juanli-msft · Answer

Hi&nbsp;Anonymous
Test based on your example.
&nbsp;
&nbsp;
&nbsp;
"rate" is&nbsp; used in a measure, "flag2" is in a calculated column
&nbsp;
rate&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;[Category]&nbsp;)&nbsp;=&nbsp;"a",&nbsp;&nbsp;&nbsp;&nbsp;DIVIDE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Sheet1[Sales]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;ALLEXCEPT&nbsp;(&nbsp;Sheet1,&nbsp;Sheet1[City]&nbsp;),&nbsp;[Category]&nbsp;=&nbsp;"a"&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;SUM&nbsp;(&nbsp;Sheet1[Sales]&nbsp;),&nbsp;ALLEXCEPT&nbsp;(&nbsp;Sheet1,&nbsp;Sheet1[City]&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;())
flag2&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;[rate]&nbsp;&gt;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;[rate]&nbsp;&lt;=&nbsp;0.3,&nbsp;&nbsp;&nbsp;&nbsp;"0%-30%",&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[rate]&nbsp;&gt;&nbsp;0.3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;[rate]&nbsp;&lt;=&nbsp;0.6,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"30%-60%",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;[rate]&nbsp;&gt;&nbsp;0.6&nbsp;&amp;&amp;&nbsp;[rate]&nbsp;&lt;=&nbsp;0.8,&nbsp;"60%-80%",&nbsp;IF&nbsp;(&nbsp;[rate]&nbsp;&gt;&nbsp;0.8,&nbsp;"&gt;80%"&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))
Best Regards
Maggie

v-juanli-msft · Answer

Hi&nbsp;Anonymous

I'm not sure I understand how the calculated bin column based up on&nbsp;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.

&nbsp;
Best Regards
Maggie

anonymous · Answer

Hi Maggie,&nbsp;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.&nbsp;for example:&nbsp;below table, city, total sale, category are predefined.&nbsp;city: 1, total sale: 100/-, category: furniture,&nbsp; 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%&nbsp;.... etc&nbsp;Furniture sale is calculated as below:Furniture Sales (%) = var x=CALCULATE(SUM(Sheet1[Sales]),FILTER(Sheet1,Sheet1[Category]="Furniture"))returnx/SUM(Sheet1[Sales])&nbsp;now "needed column" is as below(IF([Furniture Sales (%)]&gt;0 &amp;&amp; [Furniture Sales (%)]&lt;=0.30, "0% - 30%",IF([Furniture Sales (%)]&gt;0.30 &amp;&amp; [Furniture Sales (%)]&lt;=0.60,"30% - 60%",IF([Furniture Sales (%)]&gt;0.60 &amp;&amp; [Furniture Sales (%)]&lt;=0.80,"60% - 80%",IF([Furniture Sales (%)]&gt;0.80,"&gt; 80%" )))))&nbsp;solution needed for adding "needed column" in pie chart legends.&nbsp;&nbsp;Thanks,Ganesh.

lomanaho · Answer

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])RETURNIF(FurnitureSalesPercentage &gt; 0 &amp;&amp; FurnitureSalesPercentage &lt;= 0.30, "0%-30%",IF(FurnitureSalesPercentage &gt; 0.30 &amp;&amp; FurnitureSalesPercentage &lt;= 0.60, "30%-60%",IF(FurnitureSalesPercentage &gt; 0.60 &amp;&amp; FurnitureSalesPercentage &lt;= 0.80, "60%-80%",IF(FurnitureSalesPercentage &gt; 0.80, "&gt;80%", "Other"))))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.

Forum Discussion

Create Bin based on Measure value

4 Replies

Related content

Need help creating a measure

Help to create DAX Measure

Create tooltip based measure

Create a Measure to Count based on a condition

Create a measure Based on Visual values