Forum Discussion
Helper IBIN not working as expected
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!
Super UserPerAsperaAdAstr , 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-bins
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-in-power-bi/
Helper Inew 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])returnCALCULATE(SUMX(aTable,[measure 3]),FILTER(aTable,[rank 2]>=VALUES(DimRank[Lower])&&[rank 2]<=VALUES(DimRank[Upper])))Total GMV GBP BIN1 =- PerAsperaAdAstr
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!