Dynamic Banding with a Twist

[zudar]

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. I will reference to this tutorial in order to explain my new requirement. 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 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.

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: I want to divide the 'Sales dynamic' by the number of unique customers that have a 'LoyaltyPointsTotal' that surpasses a band's upper limit, independent from the row context. BUT, if the 'LoyaltyPointsTotal' of a customer doesn't surpass the band's upper limit, I want to exclude all purchases by this customer (and within a certain band) from this calculation.

Now, please refer to the sales per person below:

For example, customer Ashlee Shen has 1200 loyalty points in total and made her last purchase in the store at 1150 points. This means that (given a band size of 250), this customer should be excluded from the final band '1000-1250' for the new measure: Ashlee Shen is not part of the divisor (count of unique customers) and thus, her purchase of €5 @ 1150 loyalty points should be omitted too.

In the matrix, and I'm showing only the final two bands below, this would mean the following end result.

Gender	ProductCategory	...	750		1000
F	DRINK	...	€1	€1/4 = €0,25	€5	-
	FOOD	...	€7	€2/4 = €0,50
M	DRINK	...	€1	€1/4 = €0,25	€1	-
	FOOD	...	€4	€4/4 = €1	€4	€3/1 = €3

• All customers except Colleen Li qualify for the band '750-1000' as her LoyaltyPointsTotal of 850 doesn't surpass the upper limit of 1000. She and her purchase of €5 @ 750 loyalty points should be omitted from the new measure.
• Only Erick Lopez' LoyaltyPointsTotal of 1500 surpasses the band's upper limit of 1250 and therefore, only his purchase of €3 qualifies for the new measure.

Please let me know if anyone has any idea how to construct this new measure. I'm struggling to modify my existing 'Sales dynamic' measure (ignoring the row context and comparing to a dynamic value, a band's upper limit).

YOUR HELP IS VERY MUCH APPRECIATED! 🙂

[v-jingzhang · Accepted solution]

Hi @zudar 

Try this measure for the distinct count. Not sure if I understand the logic. When you filter out customers whose 'LoyaltyPointsTotal' doesn't surpass the band's upper limit, do the remaining customers should have a 'LoyaltyPointsTotal' that doesn't surpass the next band's upper limit?

Distinct Count = 
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,
var _table = FILTER(SUMMARIZE(FactSales,FactSales[CustomerKey],"Total Points",MAX(FactSales[LoyaltyPoints])),[Total Points]>_bandTail&&[Total Points]<=_bandTail+_bandSize)
return
COUNTROWS(_table))

Best Regards,
Community Support Team _ Jing

[zudar]

Hi @v-jingzhang!

Thank you for your response. Your measure is not giving me any results.

Also, my requirements have changed and have become simpler.

Please look at my new topic: Help! Dynamic Banding.. - Microsoft Power BI Community

I will close this one.

Thanks again!