Help! Dynamic Banding..

[Anonymous]

Hi all,

I could really use some help with an analysis I'm working on. The general idea behind the analysis is very similar to this tutorial: Dynamic Banding or Grouping in Power BI – Using DAX Measures – Choose the Size of Bins - RADACAD. My PBIX can be downloaded here: https://we.tl/t-MoVA4o5krn.

So, the dataset used in the tutorial is a DimCustomer-table with one record per customer. I added one column 'gender' that shows an 'F' for females and 'M' for males. Another important column is 'LoyaltyPointsTotal' with a total amount of loyalty points acquired per customer.

In addition, I have added a DimProducts and FactSales. In FactSales, there's a column 'LoyaltyPoints' that will be used to apply dynamic banding as described in the tutorial I referred to earlier. This column gives the (cumulative) number of loyalty points a customer had when making the purchase. The model looks like this:

In the end I'm looking for this type of matrix ('Gender' + 'ProductCategory' on the rows, 'Loyalty Band' on the columns):

Where the measure is:

Sales dynamic = 
var _minReading=MINX(ALL('Loyalty Band'),'Loyalty Band'[Loyalty Band Value])
var _maxReading=MAXX(ALL('Loyalty Band'),'Loyalty Band'[Loyalty Band Value])
var _bandSize='What is Band Size'[What is Band Size Value]
var _ReadingBandTable=GENERATESERIES(_minReading,_maxReading,_bandSize)
var _currReading=SELECTEDVALUE('Loyalty Band'[Loyalty Band])
var _bandHead=MAXX(
    FILTER(
    _ReadingBandTable,
    [Value]<=_currReading),[Value])
var _bandTail=_bandHead+_bandSize
return
if(_currReading=_bandHead,
CALCULATE(
    SUM(FactSales[Sales]),
'Loyalty Band'[Loyalty Band]>=_bandHead && 'Loyalty Band'[Loyalty Band]<_bandTail
))

HERE COMES THE TWIST...

Now, what I would like to do, is to add another measure in this matrix: for each band, I want to know how many of the 'LoyaltyPointsTotal' (of all the 'filtered'/selected customers) fit into that band. Then, I want to divide the 'Sales dynamic' by this number of 'contributed' LoyaltyPoints, on each row of the matrix and thus, not impacted by the row-context.

Now, please refer to the LoyaltyPointsTotal per person below:

For example, customer Ashlee Shen has 1200 loyalty points in total. This means that (given a band size of 250), she contributes 250 loyalty points in each band until '1000-1250'. In that final band, she contributes 200 loyalty points. If we do this for all selected customers, we have this overview:

Name	0-250	250-500	500-750	750-1000	1000-1250
Ashlee Shen	250	250	250	250	200
Caroline Coleman	250	250	250	250	100
Colleen Li	250	250	250	100	0
Erick Lopez	250	250	250	250	250
Jon Xu	250	250	250	250	200
Total	1250	1250	1250	1100	750

In the matrix, and I'm showing only the final two bands below, this would mean the following end result for the division of 'Sales dynamic' by the total number of LoyaltyPoints per band:

Gender	ProductCategory	...	750		1000
F	DRINK	...	€1	€1/1100 = €0,0009	€5	€5/750 = €0,0067
	FOOD	...	€7	€7/1100 = €0,0064	-	-
M	DRINK	...	€1	€1/1100 = €0,0009	€1	€1/750 = €0,0013
	FOOD	...	€4	€4/1100 = €0,0036	€4	€4/750 = €0,0053

Please let me know if anyone has any idea how to construct this new measure. I'm really struggling!

YOUR HELP IS VERY MUCH APPRECIATED! 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try:

Sum total = 
var _minReading=MINX(ALL('Loyalty Band'),'Loyalty Band'[Loyalty Band Value])
var _maxReading=MAXX(ALL('Loyalty Band'),'Loyalty Band'[Loyalty Band Value])
var _bandSize='What is Band Size'[What is Band Size Value]
var _ReadingBandTable=GENERATESERIES(_minReading,_maxReading,_bandSize)
var _currReading=SELECTEDVALUE('Loyalty Band'[Loyalty Band])
var _bandHead=MAXX(
    FILTER(
    _ReadingBandTable,
    [Value]<=_currReading),[Value])
var _bandTail=_bandHead+_bandSize
var _mod=IFERROR( MOD( SUM(DimCustomer[LoyaltyPointsTotal]),_currReading),0)
var _finalmod=SWITCH(TRUE(),
SUM(DimCustomer[LoyaltyPointsTotal])>_currReading-_bandSize && SUM(DimCustomer[LoyaltyPointsTotal])<_currReading,  MOD( SUM(DimCustomer[LoyaltyPointsTotal]),_bandSize),
SUM(DimCustomer[LoyaltyPointsTotal])<_currReading,0,
SUM(DimCustomer[LoyaltyPointsTotal])>=_currReading ,_bandSize,
SUM(DimCustomer[LoyaltyPointsTotal])>=_currReading&& SUM(DimCustomer[LoyaltyPointsTotal])<=_currReading+_bandSize,_mod
)
   
return
if(_currReading=_bandHead,
 CALCULATE(
_finalmod,
'Loyalty Band'[Loyalty Band]>=_bandHead && 'Loyalty Band'[Loyalty Band]<_bandTail
)
)

get the current = SUMX(ALLSELECTED('DimCustomer'),[Sum total])

get the next = CALCULATE([get the current],FILTER(ALLSELECTED('Loyalty Band'),[Loyalty Band]=MAX('Loyalty Band'[Loyalty Band])+[What is Band Size Value]))

divide = DIVIDE([Sales dynamic],[get the next])

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous

I don't think words can describe how grateful I am for your response. You literally saved me from so much headache. Seeing the solution makes me realize I wouldn't have been able to come up with it myself.

I have two questions though, if you're willing to help me a little more..

1)

I might need to include a 'LoyaltyPointsStartingTotal' per customer, in addition to 'LoyaltyPointsTotal'. This would mean that, for example, Ashlee Shen would start from 600 and end at 1200 points. The 'Sum total' should then return something like this:

Name	0-250	250-500	500-750	750-1000	1000-1250
Ashlee Shen	0	0	150	250	200

Do you have any idea how to do this?

2)

In the measure 'Sum total', the result is shifted one band higher than I showed in my table. So for example, Ashlee Shen final band is 1000-1250 with 200 points, because her total is 1200 points. In your solution, those 200 points are placed into the 1250-1500 band. After this, it seems like you created 'get the current' and 'get the next' to fix this shift.

Could you maybe explain why you did that?

In the meantime I will mark your response as the accepted solution, since it gives the exact results I was aiming for. Thank you again!!!