cancel
Showing results for
Did you mean:
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

## Re: help with custom column

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.

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  Tot Wk 1 Av Wk 01% Wk 2 Tot Wk 2 Av Wk 02% Wk 3 Tot Wk 3 Av Wk 03% Wk 4 Tot Wk 4 Av Wk 04% Wk 5 Tot Wk 5 Av Wk 05% Wk 06 Tot Wk 06 Av Wk 06% Wk 07 Tot Wk 07 Av Wk 07% Wk 08 Tot Wk 08 Av Wk 08% Wk 09 Tot Wk 09 Av Wk 09% Wk 10 Tot Wk 10 Av Wk 10% Wk 11 Tot Wk 11 Av Wk 11% Wk 12 Tot Wk 12 Av Wk 12% Wk 13 Tot Wk 13 Av Wk 13% Wk 14 Tot Wk 14 Av Wk 14% Wk 15 Tot Wk 15 Av Wk 15% Wk 16 Tot Wk 16 Av Wk 16% Wk 17 Tot Wk 17 Av Wk 17% Wk 18 Tot Wk 18 Av Wk 18% 80 14 17.50 56 12 21.43 19 3 15.79 42 12 28.57 52 31 59.62 14 10 71.43 14 6 42.86 26 19 73.08 32 30 93.75 13 11 84.62 28 26 92.9 34 33 97.06 48 47 97.92 41 39 95 34 33 97 48 48 100 13 13 100 0 0 0

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 🙂

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

#### Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors