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
    scee07
    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

     

     

     

scee07's avatar
scee07
Icon for Resolver I rankResolver I
3 years ago

Hi Chris, 

are the policy numbers in your table unique? 

You see, if I pull the measure in the visual the bin numbers are identical. And if the policy numbers are unique then the sum of BondAmount is exactly the amount in the row of the original table and the measure pulled into the visual must give the same bin number. 
if the policy number is not unique (a primary key in the table) then the bin number can be wrong. Interestingly enough you see that subtotals are evaluated by the measure and as they are the sum of eintire bin, they end up in bin 1, because they are big numbers in comparison (this is an issue of the visual, but this is an issue for later).
The the policy number not being a real primary key would be my guess. Or you do something which I cannot know.

Let me know. 

 

Best regards 

 

Christian

 

 

Chochum's avatar
Chochum
Regular Visitor
3 years ago

Thanks Christian. Hmm, the mystery continues...they are indeed unique values. In your example, are there any filters applied? Let's say you filtered for items with a Bond Amount between $5 - $10K, do the values in both the Calculated Column and Measure update in sync?

  • scee07's avatar
    scee07
    Icon for Resolver I rankResolver I
    3 years ago

    Ok, so the calculated column is never updated, because it is defined on the model table level. 

    First we should agree that if you apply no filters then you should reproduce the bin number, like I do.

    Now filters (was not filter your base table, I thought perhaps the entire table is update which would not have been an issue)

    A calculated column in a model table will not work now. It has to be a virtual table in the measure that will do the job. Or just eliminating the ALLSELECTED might work (let's see).

    I am busy today, but will work on this tomorrow. 

    Think about it: the reference for the percentile calculation is the total amount of the entire table regarded. By filtering you change the base table for the percentile calculation. 

     

    Talk to you tomorrow. Best regards Christian

    • Chochum's avatar
      Chochum
      Regular Visitor
      3 years ago

      Can't thank you enough! I'll try removing the ALLSELECTED tomorrow as well.