BIN not working as expected

[PerAsperaAdAstr]

Hi community,

I've created this Rank Measure

Ranking =

var selectedID=VALUES(DimCustomer[Id])

var aTable =

ADDCOLUMNS(

SUMMARIZE(DimCustomer,DimCustomer[Id]),"rank 2",[Rank],"measure 3",[Measure 3])

return

CALCULATE(SUMX(aTable,[rank 2]),FILTER(aTable,[rank 2]>=VALUES(DimRank[Lower])&&[rank 2]<=VALUES(DimRank[Upper])))

My aim is to Bin the ranked customers into the correct buckets but this is what I get once I remove the customer ID column.

Any ideas why? or how to fix this? Not sure why the buckets isnt working as expected.

Wrong result:

I'm expecting to see the below, once the Customer ID is removed.

Many thanks.

[amitchandak]

@PerAsperaAdAstr , Value selectedID need to used

Ranking =
var selectedID=VALUES(DimCustomer[Id])
var aTable =
ADDCOLUMNS(
SUMMARIZE(DimCustomer,DimCustomer[Id]),"rank 2",[Rank],"measure 3",[Measure 3])
return
CALCULATE(SUMX(values(selectedID),[rank 2]),FILTER(aTable,[rank 2]>=VALUES(DimRank[Lower])&&[rank 2]<=VALUES(DimRank[Upper])))

see if the above can work. moved that to sumx

also, refer

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bi...
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-i...

[PerAsperaAdAstr]

new dax didnt work

[amitchandak]

@PerAsperaAdAstr , Try like

Ranking =
CALCULATE(SUMX(VALUES(DimCustomer[Id]),[rank]),FILTER(aTable,[Rank]>=Min(DimRank[Lower])&&[rank ]<=Max(DimRank[Upper])))

[PerAsperaAdAstr]

I've got it working now. I had a filter on (Measure 3 not blank) and that hid some of the data. 

So this measure works!

Total GMV GBP TT BIN =

var aTable =

ADDCOLUMNS(

SUMMARIZE(DimCustomer,DimCustomer[Id]),"rank 2",[Rank],"measure 3",[Total GMV GBP BIN1])

return

CALCULATE(SUMX(aTable,[measure 3]),FILTER(aTable,[rank 2]>=VALUES(DimRank[Lower])&&[rank 2]<=VALUES(DimRank[Upper])))

Total GMV GBP BIN1 =

[PerAsperaAdAstr · Accepted solution]

So to summarize the solution for anyone who may need it in the future.

The aim was to generate the following Pareto Graph.

Customers are ranked based on their spend (dynamic filters applied). Then grouped into 1-100 bins based on their rank.

Each bar represent the total spend per group.

So having created rank, based on the individuals spend. Then I needed 2 measures.

1. I created the following measure, to group the total sales by rank bin. I've included a screenshot of the DimRank table I created. It is an unconnected table.

(For some reason the measure didn't work when I tried to simplify my dax any further/using variables so there is some repetition)

DimRank table looks like:

Then I created the second measure, using the previous measure I created:

This measure is then used in the line/bar chart in column values.

Please note, it may look like "rankingid>=VALUES(DimRank[Lower])&&rankingid<=VALUES(DimRank[Upper])" in both measures seem redundant but it is required so that each bar does not show All spend in every bucket.

Similar logic is applied for the cumulative%, which basically adds all spend with lower rank. For completeness, I've included below.

This is then applied in the Line values in the chart, with DimRank.Buckets as the Shared Axis.

1st measure:

2nd measure:

Happy Daxing!