topic Re: How to Summarize Percentile Bins in DAX Commands and Tips

How to Summarize Percentile Bins

Chochum — Fri, 16 Dec 2022 19:46:27 GMT

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 I put it into a visual, but can't wrap my head around how to show totals for each of the ten Bins.
So far I have this measure:

BinBondLevel = Var Bin01 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.1),ALLSELECTED(DMBond)) Var Bin02 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.2),ALLSELECTED(DMBond)) Var Bin03 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.3),ALLSELECTED(DMBond)) Var Bin04 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.4),ALLSELECTED(DMBond)) Var Bin05 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.5),ALLSELECTED(DMBond)) Var Bin06 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.6),ALLSELECTED(DMBond)) Var Bin07 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.7),ALLSELECTED(DMBond)) Var Bin08 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.8),ALLSELECTED(DMBond)) Var Bin09 = calculate(PERCENTILEX.INC(DMBond,DMBond[BondAmount],0.9),ALLSELECTED(DMBond)) return SWITCH(true(), SELECTEDVALUE(DMBond[BondAmount])<=Bin01, 1, SELECTEDVALUE(DMBond[BondAmount])<=Bin02, 2, SELECTEDVALUE(DMBond[BondAmount])<=Bin03, 3, SELECTEDVALUE(DMBond[BondAmount])<=Bin04, 4, SELECTEDVALUE(DMBond[BondAmount])<=Bin05, 5, SELECTEDVALUE(DMBond[BondAmount])<=Bin06, 6, SELECTEDVALUE(DMBond[BondAmount])<=Bin07, 7, SELECTEDVALUE(DMBond[BondAmount])<=Bin08, 8, SELECTEDVALUE(DMBond[BondAmount])<=Bin09, 9, 10)

This allows me to insert the field into my visual (below), but now I'm stuck on how to aggregate to show:
a) 10 rows, one for each Bin;
b) the count of items and sum of amounts within each Bin;
c) The Bin upper limit amount as a label

Thanks for looking!

Re: How to Summarize Percentile Bins

scee07 — Sat, 17 Dec 2022 10:57:42 GMT

Hi,

if you put your measure for a level in a calculated column in the base table 'DMBond', you have all the fields you need in one table:

BLevel = [BinBondLevel]

It will pick up the row context via calculate transform into the filter context and give you the level for each policy.

Then, if you use the matrix visual you will see the subtotals for each bin for the sum and the count as values.

As you do not seem to have millons of policies, the additional storage for the calculated column should not be an issue.

Best regards

Christian

Re: How to Summarize Percentile Bins

Chochum — Mon, 19 Dec 2022 17:44:38 GMT

Thanks Christian! When I convert the measure to a calculated field (never even realized this was possible) via:

BLevel = [BinBondLevel]
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!

Re: How to Summarize Percentile Bins

scee07 — Mon, 19 Dec 2022 20:09:02 GMT

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

Re: How to Summarize Percentile Bins

Chochum — Tue, 20 Dec 2022 16:38:46 GMT

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:

Re: How to Summarize Percentile Bins

scee07 — Tue, 20 Dec 2022 16:51:33 GMT

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

Re: How to Summarize Percentile Bins

Chochum — Tue, 20 Dec 2022 18:12:30 GMT

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!

Re: How to Summarize Percentile Bins

scee07 — Wed, 21 Dec 2022 06:37:34 GMT

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

Re: How to Summarize Percentile Bins

Chochum — Wed, 21 Dec 2022 14:17:59 GMT

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

Re: How to Summarize Percentile Bins

scee07 — Wed, 21 Dec 2022 15:05:55 GMT

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

Re: How to Summarize Percentile Bins

Chochum — Wed, 21 Dec 2022 15:13:12 GMT

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?

Re: How to Summarize Percentile Bins

scee07 — Wed, 21 Dec 2022 15:44:00 GMT

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

Re: How to Summarize Percentile Bins

Chochum — Wed, 21 Dec 2022 16:54:37 GMT

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

Re: How to Summarize Percentile Bins

scee07 — Thu, 22 Dec 2022 08:30:42 GMT

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

Re: How to Summarize Percentile Bins

MartynasBI — Fri, 26 May 2023 08:24:27 GMT

Hi All,

I would like to calculate deciles that would be responsive to several filters/slicers in the report view, which come from different tables.

Model view:

Link to data source:

https://docs.google.com/spreadsheets/d/1aRCwOmyTxZvV5P-UobNtLOpV1B7mq1jeE3SEs-FMDRE/edit#gid=1463157...

The correct example below that I would like to get:

The incorrect values I get in Power BI:

Sample code I've used:

Decile demo ChatGPT =

VAR SelectedValue = MAX('Financial items'[Value_usd])

RETURN

SWITCH(

TRUE(),

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 0.1), "1st Decile",

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 0.2), "2nd Decile",

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 0.3), "3rd Decile",

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 0.4), "4th Decile",

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 0.5), "5th Decile",

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 0.6), "6th Decile",

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 0.7), "7th Decile",

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 0.8), "8th Decile",

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 0.9), "9th Decile",

SelectedValue <= PERCENTILEX.INC('Financial items', 'Financial items'[Value_usd], 1), "10th Decile"

)

Many thanks in advance