Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

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.

 

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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.

5 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Ashish_Mathur
        Super User
        5 years ago

        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.