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,
I am having a situation where I am not getting the desired result for average value. I have to calculate the monthly average commission per sales representative based on it benchmarking will be done. I am using below statement:
AVG = CALCULATE(AVERAGE('Table'[Total Commission]),FILTER(all(Calender),Calender[DateKey]<=MAX(Calender[DateKey])))
Below is the result I am getting while pivoting the data in excel but unable to get the same in pbi.
Columns 1 to 12 are months.
Please let me know where I am making mistake.
Thanks,
Anupam
Solved! Go to Solution.
Hi @anupampandey,
I have come up with a solution for you to visualize your data in Chart using a disconneted dimension and a contextually sensative measure.
Method
1. Click 'Enter Data' and add your Bins as rows along with an Order by value
2. Create a measure that reactes to the value of the 'Bin' on Rows Columns or Filters (if you do not do this the value for each bin will always be the same)
Bin Count = var bintable = SUMMARIZE('CommissionYTD', 'CommissionYTD'[RETMST_ID], "avg", [Avg. Commission]) var bin = COUNTROWS(bintable) return if(HASONEVALUE('Groups'[Bins]), SWITCH(values('Groups'[Bins]), "A,B", CALCULATE(COUNTX(filter(bintable, [avg] > 1500), bin)), "C", CALCULATE(COUNTX(filter(bintable, [avg] >= 1000 && [avg] < 1500), bin)), "D", CALCULATE(COUNTX(filter(bintable, [avg] >= 750 && [avg] < 1000), bin)), "E", CALCULATE(COUNTX(filter(bintable, [avg] > 0 && [avg] < 750), bin)) , blank()))
The Measure does the following:
- creates a variable to store the avg commision for Rep
- creates a variable to store the count of rows in the variable table
- Uses an if condition to ensure that the 'Group'[Bins] column only contains a single value, this gaurds against using the values function on a column containing multiple values
- Uses the Switch construct to test for the value in the 'Group'[Bins] column, for each valid result a calculate formula returns the count of rows from the bintable where the [avg] value in the bintable meets the requirement (note that countx is used as we need to iterate over the rows in the table to get the correct value.
you can find the working solution here
Proud to be a Super User!
Hi @richbenmintz,
Thanks for help. I implemented in my main data model.
Again thanks a lot
Regards,
Anupam
@richbenmintz wrote:Hi @anupampandey,
I have come up with a solution for you to visualize your data in Chart using a disconneted dimension and a contextually sensative measure.
Method
1. Click 'Enter Data' and add your Bins as rows along with an Order by value
2. Create a measure that reactes to the value of the 'Bin' on Rows Columns or Filters (if you do not do this the value for each bin will always be the same)
Bin Count = var bintable = SUMMARIZE('CommissionYTD', 'CommissionYTD'[RETMST_ID], "avg", [Avg. Commission]) var bin = COUNTROWS(bintable) return if(HASONEVALUE('Groups'[Bins]), SWITCH(values('Groups'[Bins]), "A,B", CALCULATE(COUNTX(filter(bintable, [avg] > 1500), bin)), "C", CALCULATE(COUNTX(filter(bintable, [avg] >= 1000 && [avg] < 1500), bin)), "D", CALCULATE(COUNTX(filter(bintable, [avg] >= 750 && [avg] < 1000), bin)), "E", CALCULATE(COUNTX(filter(bintable, [avg] > 0 && [avg] < 750), bin)) , blank()))The Measure does the following:
- creates a variable to store the avg commision for Rep
- creates a variable to store the count of rows in the variable table
- Uses an if condition to ensure that the 'Group'[Bins] column only contains a single value, this gaurds against using the values function on a column containing multiple values
- Uses the Switch construct to test for the value in the 'Group'[Bins] column, for each valid result a calculate formula returns the count of rows from the bintable where the [avg] value in the bintable meets the requirement (note that countx is used as we need to iterate over the rows in the table to get the correct value.
you can find the working solution here
@richbenmintz wrote:Hi @anupampandey,
I have come up with a solution for you to visualize your data in Chart using a disconneted dimension and a contextually sensative measure.
Method
1. Click 'Enter Data' and add your Bins as rows along with an Order by value
2. Create a measure that reactes to the value of the 'Bin' on Rows Columns or Filters (if you do not do this the value for each bin will always be the same)
Bin Count = var bintable = SUMMARIZE('CommissionYTD', 'CommissionYTD'[RETMST_ID], "avg", [Avg. Commission]) var bin = COUNTROWS(bintable) return if(HASONEVALUE('Groups'[Bins]), SWITCH(values('Groups'[Bins]), "A,B", CALCULATE(COUNTX(filter(bintable, [avg] > 1500), bin)), "C", CALCULATE(COUNTX(filter(bintable, [avg] >= 1000 && [avg] < 1500), bin)), "D", CALCULATE(COUNTX(filter(bintable, [avg] >= 750 && [avg] < 1000), bin)), "E", CALCULATE(COUNTX(filter(bintable, [avg] > 0 && [avg] < 750), bin)) , blank()))The Measure does the following:
- creates a variable to store the avg commision for Rep
- creates a variable to store the count of rows in the variable table
- Uses an if condition to ensure that the 'Group'[Bins] column only contains a single value, this gaurds against using the values function on a column containing multiple values
- Uses the Switch construct to test for the value in the 'Group'[Bins] column, for each valid result a calculate formula returns the count of rows from the bintable where the [avg] value in the bintable meets the requirement (note that countx is used as we need to iterate over the rows in the table to get the correct value.
you can find the working solution here
It looks like for every month you will get the LTD average value, you are clearing the filter context from the calendar table and then filtering it to include all members <= the end of the month value, assuming you are using months on your columns. One thing to note as well is that if you have not filtered your table by a year then the 1-12 value will repeat for every year and the max value will be the max for each of the month slices.
Hope that makes sense
Proud to be a Super User!
Hi @richbenmintz,
Could you alter my calculation statement please that would be of great help?
Thanks,
Anupam
If you can send me the pbix file then I will try to provide you with a formula that will work for you. As a side note, have you tried the new quick measures function in power bi desktop for this measure?
Proud to be a Super User!
Hi,
Below is the pbix file.
https://drive.google.com/drive/folders/0B105l6qUOGQBWU5JUmpEUU9RTGc?usp=sharing
Thanks & Regards,
Anupam
Hi,
I am a very big fan of making things a simple as possible, if you are looking for the average for a set of periods, then you can use the following formula.
Avg. Commission = [TotalCommission]/DISTINCTCOUNT('Calender'[FYMonthSlice])
Here is a link to the modified file, link to pbix
As an aside I am not sure why you need to create a calculated table when your base table had all the data you needed.
Proud to be a Super User!
Hi @richbenmintz,
Thanks for the valuable input. I am getting the desired result with helpful suggestion. Now I am stuck while putting nested AND IF condition. Below is the screenshot for reference.
Could you look into it and suggest where I am missing out something please?
Thanks & Regards,
Anupam
please try the following formula, it uses the much easier to read switch construct, goodbye nested 'if' statements, and evaluates for a true condition. Please notice that the ordering of the conditions is very important and the function will short circuit as soon as a condition equals true. This formula also allows us not to worry about the between ranges. If an item is > than 1500 it is assigned an 'A,B' bucket, if an item is > 1000 and does not meet the previous condition it gets assigned a 'C' bucket all the way down the evaluation order. The last Parameter "E" is the default bucket and will be assigned to any item that does not match any of the prior rules.
Avg. Commission Bucket = SWITCH(TRUE(), [Avg. Commission] >1500, "A,B", [Avg. Commission] > 1000, "C", [Avg. Commission] > 750, "D", "E")
Hope this solves it for you.
Richard
Proud to be a Super User!
Hi @richbenmintz,
I tried your suggestion also but not getting the desired result. What I am getting is "E" all around?
Could you check it for me in my sample data which I shared you earlier please?
Thanks,
Anupam
I will check the formula work perfectly for me using your file, I assume the prior link will get me the latest file, if not please upload your latest working version.
Proud to be a Super User!
Hi,
I have put the updated file at same place. Please check
Thanks,
Anupam
You created a calclated column which evaluates over each row in the table, the calc I gave you should be used as a measure
Proud to be a Super User!
Thanks I have corrected my mistake. But I would like to put this bucket on chart . How can I do that?
Thanks,
Anupam
I will review this evening, have to get some work done now
Proud to be a Super User!
Hi @anupampandey,
I have come up with a solution for you to visualize your data in Chart using a disconneted dimension and a contextually sensative measure.
Method
1. Click 'Enter Data' and add your Bins as rows along with an Order by value
2. Create a measure that reactes to the value of the 'Bin' on Rows Columns or Filters (if you do not do this the value for each bin will always be the same)
Bin Count = var bintable = SUMMARIZE('CommissionYTD', 'CommissionYTD'[RETMST_ID], "avg", [Avg. Commission]) var bin = COUNTROWS(bintable) return if(HASONEVALUE('Groups'[Bins]), SWITCH(values('Groups'[Bins]), "A,B", CALCULATE(COUNTX(filter(bintable, [avg] > 1500), bin)), "C", CALCULATE(COUNTX(filter(bintable, [avg] >= 1000 && [avg] < 1500), bin)), "D", CALCULATE(COUNTX(filter(bintable, [avg] >= 750 && [avg] < 1000), bin)), "E", CALCULATE(COUNTX(filter(bintable, [avg] > 0 && [avg] < 750), bin)) , blank()))
The Measure does the following:
- creates a variable to store the avg commision for Rep
- creates a variable to store the count of rows in the variable table
- Uses an if condition to ensure that the 'Group'[Bins] column only contains a single value, this gaurds against using the values function on a column containing multiple values
- Uses the Switch construct to test for the value in the 'Group'[Bins] column, for each valid result a calculate formula returns the count of rows from the bintable where the [avg] value in the bintable meets the requirement (note that countx is used as we need to iterate over the rows in the table to get the correct value.
you can find the working solution here
Proud to be a Super User!
Hi @richbenmintz,
Thanks for help. I implemented in my main data model.
Again thanks a lot
Regards,
Anupam
@richbenmintz wrote:Hi @anupampandey,
I have come up with a solution for you to visualize your data in Chart using a disconneted dimension and a contextually sensative measure.
Method
1. Click 'Enter Data' and add your Bins as rows along with an Order by value
2. Create a measure that reactes to the value of the 'Bin' on Rows Columns or Filters (if you do not do this the value for each bin will always be the same)
Bin Count = var bintable = SUMMARIZE('CommissionYTD', 'CommissionYTD'[RETMST_ID], "avg", [Avg. Commission]) var bin = COUNTROWS(bintable) return if(HASONEVALUE('Groups'[Bins]), SWITCH(values('Groups'[Bins]), "A,B", CALCULATE(COUNTX(filter(bintable, [avg] > 1500), bin)), "C", CALCULATE(COUNTX(filter(bintable, [avg] >= 1000 && [avg] < 1500), bin)), "D", CALCULATE(COUNTX(filter(bintable, [avg] >= 750 && [avg] < 1000), bin)), "E", CALCULATE(COUNTX(filter(bintable, [avg] > 0 && [avg] < 750), bin)) , blank()))The Measure does the following:
- creates a variable to store the avg commision for Rep
- creates a variable to store the count of rows in the variable table
- Uses an if condition to ensure that the 'Group'[Bins] column only contains a single value, this gaurds against using the values function on a column containing multiple values
- Uses the Switch construct to test for the value in the 'Group'[Bins] column, for each valid result a calculate formula returns the count of rows from the bintable where the [avg] value in the bintable meets the requirement (note that countx is used as we need to iterate over the rows in the table to get the correct value.
you can find the working solution here
@richbenmintz wrote:Hi @anupampandey,
I have come up with a solution for you to visualize your data in Chart using a disconneted dimension and a contextually sensative measure.
Method
1. Click 'Enter Data' and add your Bins as rows along with an Order by value
2. Create a measure that reactes to the value of the 'Bin' on Rows Columns or Filters (if you do not do this the value for each bin will always be the same)
Bin Count = var bintable = SUMMARIZE('CommissionYTD', 'CommissionYTD'[RETMST_ID], "avg", [Avg. Commission]) var bin = COUNTROWS(bintable) return if(HASONEVALUE('Groups'[Bins]), SWITCH(values('Groups'[Bins]), "A,B", CALCULATE(COUNTX(filter(bintable, [avg] > 1500), bin)), "C", CALCULATE(COUNTX(filter(bintable, [avg] >= 1000 && [avg] < 1500), bin)), "D", CALCULATE(COUNTX(filter(bintable, [avg] >= 750 && [avg] < 1000), bin)), "E", CALCULATE(COUNTX(filter(bintable, [avg] > 0 && [avg] < 750), bin)) , blank()))The Measure does the following:
- creates a variable to store the avg commision for Rep
- creates a variable to store the count of rows in the variable table
- Uses an if condition to ensure that the 'Group'[Bins] column only contains a single value, this gaurds against using the values function on a column containing multiple values
- Uses the Switch construct to test for the value in the 'Group'[Bins] column, for each valid result a calculate formula returns the count of rows from the bintable where the [avg] value in the bintable meets the requirement (note that countx is used as we need to iterate over the rows in the table to get the correct value.
you can find the working solution here
Hi @anupampandey,
Could your try the formula below to see if it works in your scenario?
Avg. Commission Bucket = IF ( [Avg. Commission] >= 0 && [Avg. Commission] <= 750, "E", IF ( [Avg. Commission] <= 1000, "D", IF ( [Avg. Commission] <= 1500, "C", IF ( [Avg. Commission] <= 2500, "B", "A" ) ) ) )
Regards
Hi @v-ljerr-msft,
I have taken your code and trying to do the same but not getting desired result why I don't know?
I am getting only "E".
Could you look into it once again please?
Thanks,
Anupam
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |