cancel
Showing results for
Did you mean:
anupampandey Member

## 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. Sample

Columns 1 to 12 are months.

Please let me know where I am making mistake.

Thanks,

Anupam

2 ACCEPTED SOLUTIONS

Accepted Solutions

## Re: Average (per month per sales representative)

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

anupampandey Member

## Re: Average (per month per sales representative)

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

Again thanks a lot Regards,

Anupam

@richbenmintz wrote:

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:

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

17 REPLIES 17

## 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

anupampandey Member

## Re: Average (per month per sales representative)

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

Thanks,

Anupam

## 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?

anupampandey Member

## Re: Average (per month per sales representative)

Hi,

Below is the pbix file.

Thanks & Regards,

Anupam

## 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])`

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.

anupampandey Member

## Re: Average (per month per sales representative)

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 v-ljerr-msft
Microsoft

## Re: Average (per month per sales representative)

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

anupampandey Member

## Re: Average (per month per sales representative)

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

## Re: Average (per month per sales representative)

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 