Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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!
That should work for you
= IF(SUM(Table[Variance]) > 6, ROUND(DIVIDE(SUM(Table[Variance]),7) * 5,0), SUM(Table[Variance]))
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
Many thanks all,
Cheers,
Rich
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!
Just create a new column wiht the condition:
Column = IF(Table[Variance] <= 6, Table[Variance], ROUND(Table[Variance] / 7 * 5))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |