Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

Need Help
  • mauriciosotero's avatar
    mauriciosotero
    7 years ago

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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

4 Replies

  • mauriciosotero's avatar
    mauriciosotero
    Icon for Resolver III rankResolver III
    7 years ago

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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

     

    Regards.