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

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)

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: help with custom column

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.

Highlighted
Frequent Visitor

Re: help with custom column

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 🙂

 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors