Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
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?
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |