DAX calcul variations

Hi Audrey_ADAPEI35 ,

 

Of course. This is a very common challenge when doing financial analysis in Power BI. Your DAX calculation isn't actually incorrect; it's mathematically sound. The issue you're facing is a matter of financial interpretation, where an increase in expenses is typically viewed as an unfavorable event. Your current formula, which calculates the difference between the current and prior year, is the standard method for measuring the direct impact on your net profit.

 

Let's break down your results. Your expenses (Charges) increased from -10.9M€ to -11.2M€. The calculation (-11,249,124) - (-10,931,551) correctly yields -317,573 €. This negative result accurately represents the unfavorable impact on your bottom line. An increase in costs hurts profit. Likewise, when your revenue (Produits) decreases, the variance is also negative, as this is also an unfavorable event. Your total variance is therefore a coherent sum of all the favorable and unfavorable impacts.

 

If you wish to change this so that an increase in expenses appears as a positive number (to show the magnitude of the increase rather than its impact on profit), you must logically reverse the sign of the variance for expense-related accounts only. You can achieve this by creating a new measure that identifies the account type from your dimension table. Assuming you have a column named [Account Type] in your accounts dimension table that specifies "Expenses" or "Revenue", you can use the following DAX logic.

Adjusted Variance = 
VAR RawVariance = [Actuals] - CALCULATE([Actuals], SAMEPERIODLASTYEAR('Date Table'[Date]))
VAR AccountCategory = SELECTEDVALUE('Dim Accounts'[Account Type])
RETURN
    IF(
        AccountCategory = "Expenses",
        -1 * RawVariance, -- Invert the sign for expenses
        RawVariance       -- Keep the original sign for revenue and other accounts
    )

Be very careful with this approach. While it may seem more intuitive on the expense lines, it will make your Total line mathematically incorrect. The sum of the individual variances will no longer equal the variance on the Total line, because you will be adding inverted numbers to non-inverted ones. This can be very misleading.

 

The most robust and recommended best practice is to keep your original formula and use conditional formatting to improve visual clarity. Your original calculation is superior because it is additive and consistently reflects the impact on profit from top to bottom. Instead of changing the DAX, go to the formatting options for your matrix in Power BI. Under "Cell elements," apply icons or background colors to your variance measure. For example, set a rule to show a green icon for any value greater than zero (a favorable variance) and a red icon for any value less than zero (an unfavorable variance). This method gives you the best of both worlds: a visually intuitive report and data that remains mathematically and analytically sound.

 

Best regards,

Hi Audrey_ADAPEI35 ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

Hi Audrey_ADAPEI35 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

Hi Audrey_ADAPEI35 ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.