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

Average (per month per sales representative)

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.Average.PNGSample

 

 

 

 

 

 

 

 

 

Columns 1 to 12 are months.

Please let me know where I am making mistake.

 

Thanks,

Anupam

2 ACCEPTED SOLUTIONS

Accepted Solutions
richbenmintz
Advisor

Re: Average (per month per sales representative)

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

 

link to pbix

View solution in original post

Re: Average (per month per sales representative)

Hi @richbenmintz,

 

Thanks for help. I implemented in my main data model.

 

Again thanks a lot Smiley Happy

 

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

 

link to pbix



@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

 

link to pbix


 

View solution in original post

17 REPLIES 17
richbenmintz
Advisor

Re: Average (per month per sales representative)

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

Re: Average (per month per sales representative)

Hi @richbenmintz,

 

Could you alter my calculation statement please that would be of great help?

 

Thanks,

Anupam

richbenmintz
Advisor

Re: Average (per month per sales representative)

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?

Re: Average (per month per sales representative)

Hi,

 

Below is the pbix file.

 

https://drive.google.com/drive/folders/0B105l6qUOGQBWU5JUmpEUU9RTGc?usp=sharing

 

 

Thanks & Regards,

Anupam

richbenmintz
Advisor

Re: Average (per month per sales representative)

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.

 

Re: Average (per month per sales representative)

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.

 

 

Average_Condition.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

Could you look into it and suggest where I am missing out something please?

 

 

Thanks & Regards,

Anupam

 

Microsoft v-ljerr-msft
Microsoft

Re: Average (per month per sales representative)

Hi @anupampandey,

 

Could your try the formula below to see if it works in your scenario? Smiley Happy

 

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" )
        )
    )
)

r1.PNG

 

Regards

Re: Average (per month per sales representative)

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 

 

richbenmintz
Advisor

Re: Average (per month per sales representative)

Hi @anupampandey

 

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,211)