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
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

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

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