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])returnCALCULATE(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.

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!

PerAsperaAdAstr

Helper I

6 years ago

Solved

BIN not working as expected

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.

Need Help

PerAsperaAdAstr
6 years ago
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!