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
Hi,
just to be clear: if you put your measure as additional calculated column in your Power BI table (not the table visual, the model table), do you get your desired result in the column: the bin assignement for every record in your table?
When I took your measure this worked for my model table. In other words, do you face the problem in the model table or only in the visual?
Best regards
Christian
When I pull up the pertninant records in the PowerBI table, they all show the same value of 10. It appears as though the original measure works as expected when dropped into a visual (filters applied force a recalc based on what's on the screen and bins them correctly), but when I turn it into Calculated Column from the measure it no longer responds the same way to the applied filters. I also confirmed going to the filters and selecting/deselecting items has no impact the Calculated Column. Appreciate your attention!