Forum Discussion

fahadqadir's avatar
fahadqadir
Helper I
4 years ago

Dynamic Bins Size by Parameter

Hello I tried to find the Percentage of Churns by Age (Bins). I want to chaneg the age bin size by parameter. power bi file link:  https://drive.google.com/drive/folders/1YhZZ1csw_dHjBpND9uCTsEraDi...
Need Help
Show and Tell
fahadqadir's avatar
fahadqadir
Helper I
4 years ago

Yes, I use this approach but I don't know how I add my Percentage churn measure to the calculation to find % churn by dynamic bin size.

  • Migasuke's avatar
    Migasuke
    Memorable Member
    4 years ago

    Hi,
    I really like solution which indkitty posted. It basically avoid the problem with dynamic columns in a table.
    This solution seems to be better than the M parameter. In case you need % distribution I edited measure Radacad has.

     

    Count of Customers in each Band = 
var _minAge=[Min Age]
var _maxAge=[Max Age]
var _bandSize=[Band Size Value]
var _AgeBandTable=GENERATESERIES(_minAge,_maxAge,_bandSize)
var _currAge=SELECTEDVALUE('Age Band'[Age])
var _bandHead=MAXX(
    FILTER(
    _AgeBandTable,
    [Value]<=_currAge),[Value])
var _bandTail=_bandHead+_bandSize
var _allNumber = CALCULATE(COUNTROWS(DimCustomer),ALL(DimCustomer))
var _numberCount = if(_currAge=_bandHead,
CALCULATE(
    COUNTROWS(DimCustomer),
'Age Band'[Age]>=_bandHead && 'Age Band'[Age]<_bandTail
))

return
DIVIDE(_numberCount,_allNumber)

     

    Basically I created a variable, where I calculate ALL people and in return statement I divide it (to get %).

    Result looks like this: