cancel
Showing results for
Did you mean:
anupampandey Member

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

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

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

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.

anupampandey Member

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

Hi,

I have put the updated file at same place. Please check

Thanks,

Anupam

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

You created a calclated column which evaluates over each row in the table, the calc I gave you should be used as a measure

anupampandey Member

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

Thanks I have corrected my mistake. But I would like to put this bucket on chart . How can I do that?

Thanks,

Anupam

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

I will review this evening, have to get some work done now

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

Announcements   