Forum Discussion

trine_norris's avatar
trine_norris
Frequent Visitor
6 years ago

Find Max ROI By Dynamic Group

Hi everyone, I have a table that looks something like this:  Year Channel Investment Revenue 2017 Social 1000 3000 2017 Search 200 500 2017 TV 4000 15000 2018 Social 12...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

trine_norris , I am not clear Max ROI.

So ROI is


roi= divide(sum(table[revenue]),sum(table[investment]))

 

So if want to know max roi by channel by year

Max roi Channel = maxx(summarize(Table, Table[year], table[channel], "_1",[roi]),[_1])

trine_norris's avatar
trine_norris
Frequent Visitor
6 years ago

Thanks for your replies!

 

ROI is revenue/investment

however the ROI for two (or more) channels, is not just the ROI summed up. Instead you need to recalculate it, based on the aggregated investment and revenue. 

 

Therefore the issue is that i cannot precalculate ROI, as it depends on the selected channels (where i have around 20).

So i would need a dynamic table, where i can calculate a dynamic ROI based on the selected channels. 

 

Makes sense? 🙂

 

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    trine_norris what is not working the solution posted above?

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

    • trine_norris's avatar
      trine_norris
      Frequent Visitor
      6 years ago

      Hi, 

       

      The max ROI should always be found for all years, not just the selected ones. 

      And it still seems as though it calculates the ROI for each channel, and not aggregated.