Forum Discussion

RichJW's avatar
RichJW
Icon for Helper III rankHelper III
7 years ago
Solved

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

Need Help

4 Replies

  • zoloturu's avatar
    zoloturu
    Icon for Memorable Member rankMemorable Member
    7 years ago

    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 

    and SWITCH - https://docs.microsoft.com/en-us/dax/switch-function-dax

     

    Regards,
    Ruslan
    -------------------------------------------------------------------
    Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Just create a new column wiht the condition:

     

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

     

  • aaronwatt's avatar
    aaronwatt
    Frequent Visitor
    7 years ago

    That should work for you 

     

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

    • RichJW's avatar
      RichJW
      Icon for Helper III rankHelper III
      7 years ago

      Hi Anonymous, zoloturu and aaronwatt.

       

      Many thanks for your responses.

       

      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 :smileyhappy:

       

      Many thanks all,

      Cheers,

      Rich