Filtering a column in SUMX to specific date for weighted average

[Mainer04401]

Here's my issue: When showing the differences in weighted averages from one time period to another, sometimes I get unexpected results if the weights have changed between the two time periods being compared.  My potential solve for this is to compute the weight percentages using the same static time period, regardless of what time period is being filtered.  

Currently, I'm computing the weighted average as :
SUMX( 'Table',  [DiscountAmount] * [SalesVolume] ) / sum(SalesVolume)

I want both the SalesVolume column and SalesVolume measure to be filtered.  It's easy enough to filter the aggregate measure  by using something like CALCULATE(sum(SalesVolume), ALL('Calendar'))) but how do I do something similar to a column inside of SUMX?  

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Explain the question and show the expected result in a Table format.

[AnalyticPulse]

here is dax you can try :
Weighted Average =
VAR StaticSalesVolume = CALCULATE(SUM('Table'[SalesVolume]), ALL('Calendar'))
RETURN
SUMX('Table', [DiscountAmount] * CALCULATE('Table'[SalesVolume], ALL('Calendar'))) / StaticSalesVolume

If this helped, Follow this blog for more insightful information about data analytics
https://analyticpulse.blogspot.com/

Some blogs regarding dax:
https://analyticpulse.blogspot.com/2023/10/complex-real-life-dax-examples.html
https://analyticpulse.blogspot.com/2023/10/mastering-power-bi-dax.html
Please Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
Please subscribe CogniJourney On Youtube For Daily fun facts:
https://www.youtube.com/@CogniJourney

[Mainer04401]

Thanks but it's not letting me use CALCULATE without an aggregator like SUM, and if I use SUM inside a SUMX the totals are wrong because then it multiplies each row by the total sales instead of each row.  

[AnalyticPulse]

can you share snip of error 

[Mainer04401]

No error but the only things I can put inside CALCULATE are measures.  If I try to use a column it says cannot find name.  If I use SUM it will multiply every row by the total sales instead of that row's sales.  

[AnalyticPulse]

are you using the column that is connected with other tables that are being used in dax?