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

Hi richbenmintz,

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

Again thanks a lot :smileyhappy:

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

---