Measure with wrong total using multiple filters

[GuiBrum]

Hello Community, I am so excited about learning PowerBi, thank you all for the support.

The measure is calculating price effect for sales rep, customer and product.

(TY price - LY price) * LY quantity for each row.

The total is not correct, upon research I found that I could Use SUMMARIZE and IF ( HASNOVALUE functions for one of the criterias and the sum is correct if I was only using the one criteria, but I am not being able to have the summarize table with all 3 criterias above.

This is as far as I got now (summarizing by product). Thanks for the help

Price Effect Measure =
VAR PriceEffect = CALCULATE( ( 'Sales Measures'[Sales Price] - [Sales Price LY] ) * [Sales Quantity LY] )
VAR TPriceEffect = SUMMARIZE( 'Product List', 'Product List'[Product], "TPriceEffect", CALCULATE( ( 'Sales Measures'[Sales Price] - [Sales Price LY] ) * [Sales Quantity LY] ) )

Return
IF( HASONEVALUE( 'Product List'[Product] ),
PriceEffect,
SUMX( TPriceEffect, [TPriceEffect] ) )

[v-zhenbw-msft]

Hi @GuiBrum ,

How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ zhenbw

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

[v-zhenbw-msft]

Hi @GuiBrum ,

You can refer to this pbix file for the question about total incorrect.

PBIX as attached.

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.     

Best regards,

Community Support Team _ zhenbw

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

[MFelix]

Hi @GuiBrum ,

Be aware that when using this type of calculation you need to pick the value from the lowest level so if you have for example Customer, Family and Product on your table you need to do the hasonevalue on the product level.

Is the Product the smallest level of your summarization or do you have additional columns wiht lower granularity?

Can you share a sample file?