Forum Discussion

GarlonYau's avatar
GarlonYau
Icon for Helper I rankHelper I
2 years ago
Solved

Comparing Budget Vs Actual in Matrix, getting "Cannot convert Value " of type Text to type Numeric"

Hi folks!

 

I've got a data set around Revenue numbers for an organization, with Actual numbers and Budget numbers.

 

1) I've unpivoted each of these tables and combined these tables into 1 table called "Company_Append_FinStmt".

2) I've got a date table called "DateFinStmts" that has a column called "ShipDay", where if it's a weekend or a holiday, it will be = 0, otherwise it's = 1.

3) I've connected these two tables via the "Date" columns.

 

Now, here's what I'm trying to do:

I'm trying to show what the "Revenue Actual / Shipday" vs "Revenue Budget / Shipday" variances are.


I have the following measures: 

Revenue Actual = CALCULATE(SUM('Company_Append_FinStmt'[Value]),'Company_Append_FinStmt'[BI Group]="Revenue", 'Company_Append_FinStmt'[Type]="Actual")

Revenue Budget = CALCULATE(SUM('Company_Append_FinStmt'[Value]),'Company_Append_FinStmt'[BI Group]="Revenue", 'Company_Append_FinStmt'[Type]="Budget")

Revenue Actual / Day = DIVIDE(CALCULATE(SUM(Company_Append_FinStmt[Value]),Company_Append_FinStmt[BI Group]="Revenue", Company_Append_FinStmt[Type]="Actual"),[ShipDays For X Axis],"")
 
Revenue Budget / Day = DIVIDE(CALCULATE(SUM(Company_Append_FinStmt[Value]),Company_Append_FinStmt[BI Group]="Revenue", Company_Append_FinStmt[Type]="Budget"),[ShipDays For X Axis],"")

All the above display with no problems when I put it into a matrix with the date as the rows.

 

However, the second I put the the measure that compares the two using this measure:

Revenue / Day % Variance = DIVIDE([Revenue Actual / Day]-[Revenue Budget / Day],[Revenue Budget / Day], "")
 
It gives me this error:

I suspect what is happening is that if either the Revenue or Budget is missing for that particular month, then it's returning "", which it then doesn't like to display. However, what I want is that when either revenue or budget is missing, just don't show anything, keep it blank.

 

Here's the link my onedrive with the files:  Dashboard Help

 

Thank so much for you help in advance!

 



Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi GarlonYau ,
    Based on your description, this appears to be an issue with text types and numeric types not converting to each other. The error you're experiencing may stem from the way Power BI handles division by zero, or a situation where the denominator (in this case ) is missing or equal to zero. When using the Divide function, the third argument provides a way to handle errors or division by zero. However, specifying the empty string ("") as an alternate result can sometimes lead to unexpected behavior, especially when Power BI requires a numeric value. You can modify the following dax to this

    Revenue / Day % Variance = 
VAR BudgetPerDay = [Revenue Budget / Day]
VAR ActualPerDay = [Revenue Actual / Day]
RETURN
IF(
    ISBLANK(BudgetPerDay) || BudgetPerDay = 0,
    BLANK(),
    DIVIDE(ActualPerDay - BudgetPerDay, BudgetPerDay)
)

    For more details on using the divide function you can check out this documentation
    DIVIDE function (DAX) - DAX | Microsoft Learn

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi GarlonYau ,
    Based on your description, this appears to be an issue with text types and numeric types not converting to each other. The error you're experiencing may stem from the way Power BI handles division by zero, or a situation where the denominator (in this case ) is missing or equal to zero. When using the Divide function, the third argument provides a way to handle errors or division by zero. However, specifying the empty string ("") as an alternate result can sometimes lead to unexpected behavior, especially when Power BI requires a numeric value. You can modify the following dax to this

    Revenue / Day % Variance = 
VAR BudgetPerDay = [Revenue Budget / Day]
VAR ActualPerDay = [Revenue Actual / Day]
RETURN
IF(
    ISBLANK(BudgetPerDay) || BudgetPerDay = 0,
    BLANK(),
    DIVIDE(ActualPerDay - BudgetPerDay, BudgetPerDay)
)

    For more details on using the divide function you can check out this documentation
    DIVIDE function (DAX) - DAX | Microsoft Learn

    Best regards,

    Albert He

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly