cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
macbar Frequent Visitor
Frequent Visitor

Apply different percentage value depending on Region

Hi,

 

I'm hoping to get some help with this problem. I have sales regions where we calculate gross and net revenues using measures. We then also apply a sales degradation percentage on the net revenue. I can apply a percentage degradation across all of the reps but I would like to be able to apply a 5% degradation to North, South and West and a 2% specifically to East.

 

RegionGross RevenueNet RevenueAfter Degradation
North120000112000106400
South180000108000102600
East240000202000191900
West1100009800093100

 

Any ideas how this would be possible?

 

Thank you.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Apply different percentage value depending on Region

Hi @macbar ,

 

If the "After Degradation" is a column, you can use like this:

 

After Degradation = 'Table'[Net Revenue]*(1-SWITCH('Table'[Region],"North",0.05,"South",0.05,"West",0.05,"East";0.02,0))

 

If you need a mesasure, I recomend you create a table Region (if you haven't done) and add a column 'Degradation %'. So the measure for 'After Degradation' will be = Net Revenue*(1-[Degradation %])

 

View solution in original post

Super User
Super User

Re: Apply different percentage value depending on Region

Hi,

Create a 2 columns Table with Region in column1 and Degratation % in column 2.  Let's call this Table, Table2.  Create a relationship from the Region column in Table1 to the Region column in Table2.  To your visual, drag Region from Table2.  Write this measure and drag it to your visual

=[Net Revenue]*(SUM(Table2[Degradation %])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4

Re: Apply different percentage value depending on Region

Hi @macbar ,

 

If the "After Degradation" is a column, you can use like this:

 

After Degradation = 'Table'[Net Revenue]*(1-SWITCH('Table'[Region],"North",0.05,"South",0.05,"West",0.05,"East";0.02,0))

 

If you need a mesasure, I recomend you create a table Region (if you haven't done) and add a column 'Degradation %'. So the measure for 'After Degradation' will be = Net Revenue*(1-[Degradation %])

 

View solution in original post

Super User
Super User

Re: Apply different percentage value depending on Region

Hi,

Create a 2 columns Table with Region in column1 and Degratation % in column 2.  Let's call this Table, Table2.  Create a relationship from the Region column in Table1 to the Region column in Table2.  To your visual, drag Region from Table2.  Write this measure and drag it to your visual

=[Net Revenue]*(SUM(Table2[Degradation %])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

macbar Frequent Visitor
Frequent Visitor

Re: Apply different percentage value depending on Region

Thank you both! Both options worked for me. I went with the Region table with degradation % values.

 

Regards.

Super User
Super User

Re: Apply different percentage value depending on Region

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 446 members 4,496 guests
Please welcome our newest community members: