Forum Discussion

Chochum's avatar
Chochum
Regular Visitor
3 years ago
Solved

How to Summarize Percentile Bins

Hi all! Trying to create a table showing total counts and sum of amounts for 10 "Percentile" bands. I've managed to create a measure which will dynamically update a percentile value for each row when...
  • scee07's avatar
    3 years ago

    Hi Chris, here is my outline of a solution:
    the first observation is, when the calculated column is gone, then measures cannot (to my knowledge) be rows or colunns of visuals. Thus, I create a table all bin and policy number combinations:

    // a table with the bin numbers
    Bins =
    SELECTCOLUMNS ( GENERATESERIES ( 1, 10, 1 ), "Bin", [Value] )
    // all combinations of values
    BinBondCombinations =
    CROSSJOIN (
        SELECTCOLUMNS ( Bins, "Bin", Bins[Bin] ),
        SUMMARIZE ( ALL ( DMBond ), DMBond[PolicyNumber] )
    )

    Idea is to check for each combination, if the amount for the policy is in the bin.
    The we have our slicer that can select amount ranges (the standard slicer applied to the bond amount). We need 2 measures for the max and min amount for the selected bond amount:

    MaxAmount =
    MAX ( DMBond[BondAmount] )
    MinAmount =
    MIN ( DMBond[BondAmount] )

    Now your measure  is modified to pick up the selected bin and policy number from the combinations:

    PolicyIsinBinAmount = 
    VAR thisPolicyNumber = SELECTEDVALUE(BinBondCombinations[PolicyNumber])
    var thisBin = SELECTEDVALUE(BinBondCombinations[Bin])
    var thisAmount = CALCULATE(FIRSTNONBLANK(DMBond[BondAmount],0), Filter(All(DMBond), DMBond[PolicyNumber] = thisPolicyNumber))
    var isInRange = thisAmount >= [MinAmount] && thisAmount <= [MaxAmount]
    
    Var Bin01 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.1),DMBond)
    Var Bin02 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.2),DMBond)
    Var Bin03 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.3),DMBond)
    Var Bin04 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.4),DMBond)
    Var Bin05 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.5),DMBond)
    Var Bin06 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.6),DMBond)
    Var Bin07 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.7),DMBond)
    Var Bin08 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.8),DMBond)
    Var Bin09 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.9),DMBond)
    
    var calculatedBin = SWITCH(true(),
    thisAmount<=Bin01, 1,
    thisAmount<=Bin02, 2,
    thisAmount<=Bin03, 3,
    thisAmount<=Bin04, 4,
    thisAmount<=Bin05, 5,
    thisAmount<=Bin06, 6,
    thisAmount<=Bin07, 7,
    thisAmount<=Bin08, 8,
    thisAmount<=Bin09, 9,
    10)
    return 
    
    if(isInRange && (thisBin = calculatedBin), thisAmount)
    

    If this is not the right amount for the bin  a blank() is returned.

    The DMBond is now restricted to the amounts in the range selected. This means your bins should adjust dynamically.

    Now, you can pull the combinations of bin and policy number in the rows of the matrix and the measure in the value section. However, the subtotals are not working then (see screenshot below). 

    We have to wrap the measure in a summable measure (this is a pure technically)

    PolicyIsInBinAmountWrapped =
    VAR tab =
        SUMMARIZE (
            BinBondCombinations,
            BinBondCombinations[Bin],
            BinBondCombinations[PolicyNumber],
            "measure", [PolicyIsinBinAmount]
        )
    RETURN
        SUMX ( tab, [measure] )

     

    This is not tested. Let me know what you can make of this.
    Best regards 
    Christian