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
Anonymous
Not applicable

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
mauriciosotero
Resolver III
Resolver III

Hi @Anonymous ,

 

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

Ashish_Mathur
Super User
Super User

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
Anonymous
Not applicable

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

 

Regards.

You are welcome.


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

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/
mauriciosotero
Resolver III
Resolver III

Hi @Anonymous ,

 

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 %])

 

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.