cancel
Showing results for
Did you mean:
Regular Visitor

## If or Switch to filter

Hi,

I'm looking for a formula to do some counting in a column of numbers, but I'm struggling.

I need it to say this.

If data in column "Variance" = <6 leave then as it is, else divide the number by 7 and multiply by 5.

So, if the data in column "Variance" is 3, for example, then it will stay as 3. If it is 48, then it will display 34 (rounded up or down).

I've browsed a lot of similar formulae on this site, but nothing so specific as it will help. I've also seen lots of suggestions for the Switch function, but not sure if it applies here.

Many thanks,

Rich

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

## Re: If or Switch to filter

Hi @RichJW,

You can use either IF either SWITCH:

```Custom =
IF(
[Variance] <= 6,
[Variance],
ROUNDDOWN([Variance] * 5 / 7, 0)
)```
```Custom2 =
SWITCH(
TRUE(),
[Variance]<= 6, [Variance],
ROUNDDOWN([Variance] * 5 / 7,0)
)```

Reference to

IF explanation - https://docs.microsoft.com/en-us/dax/if-function-dax

Regards,
Ruslan
-------------------------------------------------------------------

4 REPLIES 4
Member

## Re: If or Switch to filter

Just create a new column wiht the condition:

`Column = IF(Table[Variance] <= 6, Table[Variance], ROUND(Table[Variance] / 7 * 5))`

Highlighted

## Re: If or Switch to filter

Hi @RichJW,

You can use either IF either SWITCH:

```Custom =
IF(
[Variance] <= 6,
[Variance],
ROUNDDOWN([Variance] * 5 / 7, 0)
)```
```Custom2 =
SWITCH(
TRUE(),
[Variance]<= 6, [Variance],
ROUNDDOWN([Variance] * 5 / 7,0)
)```

Reference to

IF explanation - https://docs.microsoft.com/en-us/dax/if-function-dax

Regards,
Ruslan
-------------------------------------------------------------------

Frequent Visitor

## Re: If or Switch to filter

That should work for you

= IF(SUM(Table[Variance]) > 6, ROUND(DIVIDE(SUM(Table[Variance]),7) * 5,0), SUM(Table[Variance]))

Regular Visitor

## Re: If or Switch to filter

Hi @SPG, @zoloturu and @aaronwatt.

I tried SPG's first and received an error regarding the minimum argument count in the ROUND function, however I quickly moved onto zoloturu's and that one worked perfectly for me.

I'll try aaronwatt's as well, as it's always nice to have options

Many thanks all,

Cheers,

Rich

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,308 guests
Recent signins: