Forum Discussion
Create Bin based on Measure value
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.
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