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

Re: Average (per month per sales representative)

Hi @richbenmintz,

 

I tried your suggestion also but not getting the desired result. What I am getting is "E" all around?Average_Condition_1.PNG

 

Could you check it for me in my sample data which I shared you earlier please?

 

Thanks,

Anupam 

richbenmintz
Advisor

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.

Re: Average (per month per sales representative)

Hi,

 

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

 

Thanks,

Anupam

richbenmintz
Advisor

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

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

richbenmintz
Advisor

Re: Average (per month per sales representative)

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

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

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 (4,951)