Forum Discussion

Coffeeaddict's avatar
Coffeeaddict
Frequent Visitor
6 years ago

help with custom column

trying to get this formula from excel to work in BI. any ideas?

 

ROUNDUP(MAX(
IF([Wk 1 Tot]=0,-100,[Wk 1 Av]-([Wk 1 Tot]*0.1)),
IF([Wk 2 Tot]=0,-100,[Wk 2 Av]-([Wk 2 Tot]*0.1)),
IF([Wk 3 Tot]=0,-100,[Wk 3 Av]-([Wk 3 Tot]*0.1)),
IF([Wk 4 Tot]=0,-100,[Wk 4 Av]-([Wk 4 Tot]*0.1)),
IF([Wk 5 Tot]=0,-100,[Wk 5 Av]-([Wk 5 Tot]*0.1)),
IF([Wk 06 Tot]=0,-100,[Wk 06 Av]-([Wk 06 Tot]*0.1)),
IF([Wk 07 Tot]=0,-100,[Wk 07 Av]-([Wk 07 Tot]*0.1))),0)

Need Help

2 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Coffeeaddict ,

    I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Coffeeaddict's avatar
      Coffeeaddict
      Frequent Visitor
      6 years ago
      Wk 1  TotWk 1 AvWk 01%Wk 2 TotWk 2 AvWk 02%Wk 3 TotWk 3 AvWk 03%Wk 4 TotWk 4 AvWk 04%Wk 5 TotWk 5 AvWk 05%Wk 06 TotWk 06 AvWk 06%Wk 07 TotWk 07 AvWk 07%Wk 08 TotWk 08 AvWk 08%Wk 09 TotWk 09 AvWk 09%Wk 10 TotWk 10 AvWk 10%Wk 11 TotWk 11 AvWk 11%Wk 12 TotWk 12 AvWk 12%Wk 13 TotWk 13 AvWk 13%Wk 14 TotWk 14 AvWk 14%Wk 15 TotWk 15 AvWk 15%Wk 16 TotWk 16 AvWk 16%Wk 17 TotWk 17 AvWk 17%Wk 18 TotWk 18 AvWk 18%
      801417.50561221.4319315.79421228.57523159.62141071.4314642.86261973.08323093.75131184.62282692.9343397.06484797.9241399534339748481001313100000

       

      sample data above, The formula is to work out  how many items in the Av column is above or below 10% of the Tot column per week but return only the value for the best week and only interested in weeks 1 to 7.  

       

      In the data above the best week would be week 6.  14 items total this week so the AV column to hit 10% would be 1.4. 10 are in the Av column so this is 8.4 above what is required rounded up to return a value of 9

       

      the formula needs to do the calulation above basically for each week 1-7 and return the highest value.

       

      Hope that makes sense 🙂