How to create Bins for a Quick Measure?

[Anonymous]

Hi, 
Re-posting this since my last post seems to have been 'thin-aired'.

BACKGROUND: I have a couple of Quick Measures (Avg Monthly Billed Amt, and Avg Monthly TX Vol ) in my table (Master). I noticed that "GROUP BY" option is not available for a Measure or a Quick Measure. So, I created a "SWITCH" function as below:

Monthly Billed Amt Bins = SWITCH(
TRUE(),
[Avg Monthly Billed Amt]<26,"Up to $25"
,[Avg Monthly Billed Amt]<51,"$26-$50"
,[Avg Monthly Billed Amt]<76,"$51-$75"
,[Avg Monthly Billed Amt]<101,"$76-$100"
,[Avg Monthly Billed Amt]<126,"$101-$125"
,[Avg Monthly Billed Amt]<151,"$126-$150"
,[Avg Monthly Billed Amt]<201,"$151-$200"
,[Avg Monthly Billed Amt]<251,"$201-$250"
,[Avg Monthly Billed Amt]<301,"$251-$300"
,[Avg Monthly Billed Amt]<501,"$301-$500"
,"$501+")

AND, another one as:

Monthly Transaction Bins = SWITCH(
TRUE(),
[Avg Monthly TX Vol]<6,"Up to 5 TX"
,[Avg Monthly TX Vol]<11,"6-10 TX"
,[Avg Monthly TX Vol]<21,"11-20 TX"
,[Avg Monthly TX Vol]<41,"21-40 TX"
,[Avg Monthly TX Vol]<61,"41-60 TX"
,[Avg Monthly TX Vol]<81,"61-80 TX"
,[Avg Monthly TX Vol]<101,"81-100 TX"
,[Avg Monthly TX Vol]<121,"101-120 TX"
,[Avg Monthly TX Vol]<201,"121-200 TX"
,"200+ TX")

ISSUE: I am NOT getting the desired results. When you look at the accounts (see image below), the client ID "AAR17105" shows in multiple Avg Monthly Billed Amt, and Avg Monthly TX Vol bins.

EXPECTED RESULTS: I am trying to get all the accounts show under one respective bin, only. In this instance, Client ID: "AAR17105" has an overall avg monthly transaction (TX) volume of 447, and avg. monthly billed amount of $544.55. So, it should only show in that one bin each for TX Vol and Billed Amt. 

So, Monthly Transaction Bin will be 200+ TX, and Monthly Billed Amt Bin should be $501+. Last row of the sample data below shows overall calculated averages of 447 transactions and 544.55 billed amount.

Any help or guidance is greatly appreciated.

SAMPLE DATA:

Client ID	Month_Year	Running total TX	Running total $$	Avg Monthly TX Vol	Avg Monthly Billed Amt	Monthly Transaction Bins	Monthly Billed Amt Bins
AAR17105	Apr-18	626	780	626	780	200+ TX	$501+
AAR17105	Apr-19	13	76.8	13	76.8	11-20 TX	$76-$100
AAR17105	Aug-18	888	989.6	888	989.6	200+ TX	$501+
AAR17105	Dec-18	162	273.2	162	273.2	121-200 TX	$251-$300
AAR17105	Feb-18	382	527.2	382	527.2	200+ TX	$501+
AAR17105	Feb-19	39	113.2	39	113.2	21-40 TX	$101-$125
AAR17105	Jan-18	465	626.8	465	626.8	200+ TX	$501+
AAR17105	Jan-19	52	131.4	52	131.4	41-60 TX	$126-$150
AAR17105	Jul-18	856	964	856	964	200+ TX	$501+
AAR17105	Jul-19	1	60	1	60	Up to 5 TX	$51-$75
AAR17105	Jun-18	1006	1082	1006	1082	200+ TX	$501+
AAR17105	Jun-19	1	60	1	60	Up to 5 TX	$51-$75
AAR17105	Mar-18	444	611.6	444	611.6	200+ TX	$501+
AAR17105	Mar-19	48	125.8	48	125.8	41-60 TX	$101-$125
AAR17105	May-18	569	734.4	569	734.4	200+ TX	$501+
AAR17105	May-19	1	60	1	60	Up to 5 TX	$51-$75
AAR17105	Nov-18	872	976.8	872	976.8	200+ TX	$501+
AAR17105	Oct-18	1143	1136.8	1143	1136.8	200+ TX	$501+
AAR17105	Sep-18	922	1016.8	922	1016.8	200+ TX	$501+
Total		8490	10,346.40	447	544.55

Thank you.

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your requirement, I guess that your understanding of the tree map is not very clear. The tree map is used to visually display the proportion of the number of certain members to the whole. If you put this label field in the tree map, It only displays the count of this item you selected. I suggest you use two Card charts to display separately the tag of [Billed Amt] and [TX], and it will visually display tags of the [Account Number] you selected, you can try these measures:

Monthly Billed Amt Bins =

var _total_avg=CALCULATE(

    AVERAGE('Master'[Total_Billed_Amount]),ALLEXCEPT('Master','Master'[Account_Number]))

var _result=

SWITCH(

TRUE(),

_total_avg<26,"Up to $25"

,_total_avg<51,"$26-$50"

,_total_avg<76,"$51-$75"

,_total_avg<101,"$76-$100"

,_total_avg<126,"$101-$125"

,_total_avg<151,"$126-$150"

,_total_avg<201,"$151-$200"

,_total_avg<251,"$201-$250"

,_total_avg<301,"$251-$300"

,_total_avg<501,"$301-$500"

,"$501+")

return _result

Monthly Transaction Bins =

var _total_avg=CALCULATE(

    AVERAGE('Master'[Total_Units_of_Transaction]),ALLEXCEPT('Master',Master[Account_Number]))

var _result=

SWITCH(

TRUE(),

_total_avg<6,"Up to 5 TX"

,_total_avg<11,"6-10 TX"

,_total_avg<21,"11-20 TX"

,_total_avg<41,"21-40 TX"

,_total_avg<61,"41-60 TX"

,_total_avg<81,"61-80 TX"

,_total_avg<101,"81-100 TX"

,_total_avg<121,"101-120 TX"

,_total_avg<201,"121-200 TX"

,"200+ TX")   

    return _result

And you can get what you want, like this:

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @v-robertq-msft . I was also able to create two columns, in addition to measures, and use those columns for tree map. The script you provided works both ways. I was really having a hard time getting the averages right when using a column vs. measure. Your code seems to work great for a measure and columns.

Thank you.

[Ashish_Mathur]

Hi,

That table looks like your result table.  Share the inout table.  Also, for each row of the Monthly Transaction Bins for Client ID AAR17105, you want to show 447.  Am i correct?

[Anonymous]

Thanks @Ashish_Mathur . Here is the ultimate goal I am trying to accomplish. 

When I select an account from the table in the example below, I want that account to show in ONLY one Monthly TX Vol bin, and ONLY in one Monthly Billed Amt bin. Currently, when I select any account, most, if not all, show in multiple bins. In the example screenshot below, highlighted calculations are correct, and should, ultimately, be determining which Monthly TX Vol bin, and which Monthly Billed Amt bin should this account show in.

So, I expect to see AAR17105 in the $501+ and the 200+ TX bins, respectively, and only.

Adding link to the pbix file for review: https://drive.google.com/file/d/1kBKWY6J3NKM9OOx5gZKmocVW-khu6aP4/view?usp=sharing

Thanks for all the help. I really appreciate it.

Asad.

[Ashish_Mathur]

Hi,

Follow the Dynamic segmenation technique discussed here - https://www.daxpatterns.com/dynamic-segmentation/.  Your method is incirrect because "Monthly Billed Amt Bins" is a calculated column formula in which you have fed a measure as an input.  Also, calculated column formulas do not refresh automatically when an item in a filter/slicer is selected.