SSAS Calculatted formula

Marius van Dyk 21 Reputation points
2022-03-04T13:33:09.31+00:00

I have a calculated formula in SSAS which calculates the Budget Variance. On Powerbi report some of the values says "Ïnfinite". The formula as follows:

'iif(
( ([Measures].[Budget] - [Measures].[Actual])
/ [Measures].[Budget]) < 0,
( ([Measures].[Budget] - [Measures].[Actual])
/ [Measures].[Budget])*-1,
( ([Measures].[Budget] - [Measures].[Actual])
/ [Measures].[Budget]))'

Any advice on how to change this formula to cater for the Infinite values?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,807 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,813 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,247 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions
0 comments
Accepted answer
  1. ZoeHui-MSFT 33,296 Reputation points
    2022-03-07T06:02:16.497+00:00

    Hi @Marius van Dyk ,

    About the values says infinite, it may due to the [Measures].[Budget] contains zero or blank.

    You may use DIVIDE function to avoid this.

    Details please refer to ：dax-divide-function-operator

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-03-04T13:39:12.787+00:00

    Create new formulaire

    0 comments
  2. Alexei Stoyanovsky 3,416 Reputation points
    2022-03-05T12:09:36.18+00:00

    ABS ( DIVIDE ( [Measures].[Budget] - [Measures].[Actual], [Measures].[Budget] ) )

    0 comments