cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

How to create Bins for a Quick Measure?


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.

asadr_1-1603311253383.png

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 IDMonth_YearRunning total TXRunning total $$Avg Monthly TX VolAvg Monthly Billed AmtMonthly Transaction BinsMonthly Billed Amt Bins
AAR17105Apr-18626780626780200+ TX$501+
AAR17105Apr-191376.81376.811-20 TX$76-$100
AAR17105Aug-18888989.6888989.6200+ TX$501+
AAR17105Dec-18162273.2162273.2121-200 TX$251-$300
AAR17105Feb-18382527.2382527.2200+ TX$501+
AAR17105Feb-1939113.239113.221-40 TX$101-$125
AAR17105Jan-18465626.8465626.8200+ TX$501+
AAR17105Jan-1952131.452131.441-60 TX$126-$150
AAR17105Jul-18856964856964200+ TX$501+
AAR17105Jul-19160160Up to 5 TX$51-$75
AAR17105Jun-181006108210061082200+ TX$501+
AAR17105Jun-19160160Up to 5 TX$51-$75
AAR17105Mar-18444611.6444611.6200+ TX$501+
AAR17105Mar-1948125.848125.841-60 TX$101-$125
AAR17105May-18569734.4569734.4200+ TX$501+
AAR17105May-19160160Up to 5 TX$51-$75
AAR17105Nov-18872976.8872976.8200+ TX$501+
AAR17105Oct-1811431136.811431136.8200+ TX$501+
AAR17105Sep-189221016.89221016.8200+ TX$501+
Total 849010,346.40447544.55  

Thank you.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi, @asadr 

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:

v-robertq-msft_0-1603416610993.png

 

 

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.

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

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.

 
 
 
 
 
 

Scree-10-21 224252.png

 

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.

Highlighted

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Community Support
Community Support

Hi, @asadr 

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:

v-robertq-msft_0-1603416610993.png

 

 

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.

View solution in original post

Highlighted

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors