Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anupampandey
Helper III
Helper III

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.SampleSample

 

 

 

 

 

 

 

 

 

Columns 1 to 12 are months.

Please let me know where I am making mistake.

 

Thanks,

Anupam

2 ACCEPTED SOLUTIONS

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

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
Solution Sage
Solution Sage

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi @richbenmintz,

 

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

 

Thanks,

Anupam

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?



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi,

 

Below is the pbix file.

 

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

 

 

Thanks & Regards,

Anupam

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.

 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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

 

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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 

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.



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Hi,

 

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

 

Thanks,

Anupam

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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

 

Thanks,

Anupam

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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


 

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

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 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.