Forum Discussion

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

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

Resolver I

3 years ago

Hi,

let's recap this on order to see if we are really on the same page:

I have a toy table 'DMBond' with policy numbers from 1 to 100 and some random bond amount values.

The measure BinBondLevel is exactly your measure. The calculated column is just your measure put in this calculated column. You see below that this is putting every record on one of the 10 bins.

This is the very definition of binning: take a record put it in one of the bins.

Now I just use the matrix visual to show the transactions per bin and the subtotals:

It seems you want to present something differently. At the moment I do not understand what else, as the maximum information you have is the transactions per bin. And your measure put in the calculated column does this.
Let me know and we solve this.
Best regards
Christian

Chochum

Regular Visitor

3 years ago

This solves one of my issues, and I can now easily group, subtotal, and provide counts by Bins. But the velues I see still don't seem to do what I would expect. If you add BinBondLevel to your Matrix, would the values always match those in ColumnFromOriginalMeasure?
In my data, if I filter down to a small population of rows, I would always expect to see one row with a value 10 (max BondAmount for the selected population), and another with value 1 (min BondAmount). I rarely do.

Here's an example with a small handful of Bonds where the ColumnFromOriginalMeasure (called BLevel on mine, but same thing) only has Bin values of 8 and 10. When I add the original measure as an additional value, the correct Bin is displayed.

As another validation step I created 9 measures, one for each Bin/Percentile level, just so I could ensure they updated correctly depending on my filtering. They do:

Super curious what you find if you add BinBondLevel to your matrix, and what happens to those values if you filter down your populated values.

Thanks Christian, appreciate you joining me on this journey lol
Chris

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

Forum Discussion

How to Summarize Percentile Bins

Related content

Age Bins

Workspace recycle bin

Parameter in summarize

Don't Summarize....still summarizes...?

Percentile Bins, Group by Year