Forum Discussion
How to Summarize Percentile Bins
- 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
Thanks Christian! When I convert the measure to a calculated field (never even realized this was possible) via:
It seems to evaluate at the individual row level and sets a BLevel value of 10 see below). Similarly, the matrix visual groups them all into a BLevel of 10.
Any suggestions for next steps? Thanks!
Hi,
indpendent from the visual you should have the table in Power BI with the columns
PolicyNumber, BondAmount, and BLevel as your measure in the calculated column will assign the BLevel to every record you have (this is every policy number).
If this is the case then the matrix visual should have the columns BLevel and Policy Number in the row section and the BondAmount in the value section of the visual. Then you have for every bin level the breakdown of the policies in the bin and the subtotal of the amount per bin.
This is at least what I reproduced in my test file.
If this is not what you need let me know and I have a look at it tomorrow.
What you have above as screen shot looks like the table visual not the matrix visual.
Best regards
Christian
- Chochum3 years agoRegular Visitor
Thanks Christian,
I'd only listed as a table as it made the calculations more visible...when I structure the matrix in the way you describe, it groups everyone into Bin 10:
Included the original BinBondLevel field in this matrix just to show that measure is still calculating as expected, it's just when I add it as a calculated column in the table does it convert to a value of 10: