The waterfall chart shows wrong results

DAX DAX Calculations
1

The waterfall chart shows wrong results. Please help

Sample
Sample1217Ă—504 29.3 KB

Sample.pbix (386.3 KB)

2

Hello @Harris,

Thank You for posting your query onto the Forum.

To achieve this type of result you’ll be required to calculate the formulas at individual level and then club together. Below are the formulas provided for the reference -

Value Per Transaction AC CY - Detal = 
IF( SELECTEDVALUE( 'Sample'[Channel] ) = "Detal" , 
    DIVIDE( SUM( 'Sample'[Revenue] ) , SUM( 'Sample'[Transaction] ) , 0 ) )

Value Per Transaction AC CY - Ecom = 
IF( SELECTEDVALUE( 'Sample'[Channel] ) = "Ecom" , 
    DIVIDE( SUM( 'Sample'[Revenue] ) , SUM( 'Sample'[Transaction] ) , 0 ) )

Value Per Transaction AC CY = 
DIVIDE( [Value Per Transaction AC CY - Detal] + [Value Per Transaction AC CY - Ecom] , 2 , 0 )

Value Per Transaction AC LY - Detal = 
IF( SELECTEDVALUE( 'Sample'[Channel] ) = "Detal" , 
    DIVIDE( [Revenue_LY] , [Transaction_LY] , 0 ) )

Value Per Transaction AC LY - Ecom = 
IF( SELECTEDVALUE( 'Sample'[Channel] ) = "Ecom" , 
    DIVIDE( [Revenue_LY] , [Transaction_LY] , 0 ) )

Value Per Transaction AC LY = 
DIVIDE( [Value Per Transaction AC LY - Detal] + [Value Per Transaction AC LY - Ecom] , 2 , 0 )

So now, you can have the desired results that you’re trying to achieve. Below is the screenshot provided for the reference -

Result
Result1277Ă—541 13.3 KB

I’m also attaching the working of the PBIX file for the reference.

Quick Note: In the table, you’ve maintained the decimal figures upto 2 places but the Waterfall Chart has only option of decimal figures upto 1 place. If you change the decimal figure in table upto 1 place then the figures will be almost very close to a match.

Hoping you find this useful and meets your requirements that you’re looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Sample - Harsh.pbix (405.0 KB)

4 Likes
3

Hi @Harris, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!