Forum Discussion

tfr111's avatar
tfr111
Frequent Visitor
1 year ago
Solved

Removing date filter

Hello, I have a seemingly simple problem I cannot solve.   I would like to calculcate the all time average sales price (i.e. for the full timeframe of my data) and the individual average sales pri...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi tfr111 ,

     

    Thank you for confirming the earlier results. The current calculation only iterates at the customer level, which leads to discrepancies in monthly subtotals when dates are shown in the visual. To resolve this, update the measure to iterate over both the date and customer context using the SUMMARIZE() function. This will ensure that monthly subtotals match the sum of customer rows, yearly totals add up correctly, and the all-time average price stays consistent across all date levels. Use the following DAX:

    New Mix Sales (Correct Dates) =
VAR _fixedAvgPrice =
    CALCULATE(
        DIVIDE(SUM('Data'[Sales]), SUM('Data'[Volume])),
        REMOVEFILTERS('Date')    
    )
RETURN
    SUMX(
        SUMMARIZE(
            'Data',
            'Date'[Year],
            'Date'[Month Number],
            'Data'[Reporting Customer]
        ),
        _fixedAvgPrice * CALCULATE(SUM('Data'[Volume]))
    )

    I hope this will resolve your issue, if you need any further assistance, feel free to reach out.

     

    Thank you.

     

tfr111's avatar
tfr111
Frequent Visitor
1 year ago

Thank you very much, both! Adding a seperate date table did the trick.

 

One follow-up question:
I want to use the all-time average price to calculate a sales figure: New Mix Sales = actual volume x all-time average price).

 

However, the New Mix Sales does not sum up correctly. 

 

 

New Mix Sales = [All-time Avg Price]*[Volume]

Volume = SUM('Data'[Volume])

All-time Avg Price = 
CALCULATE(
    [Avg Price],
    ALL('Date')
)

Avg Price = 
DIVIDE (
    SUM('Data'[Sales]),
    SUM('Data'[Volume])
)

 

I tried using SUMX on New Mix Sales, but this didnt work properly.

  • MasonMA's avatar
    MasonMA
    Super User
    1 year ago

    Hi, 


    Since you have created your Date table, you may try

    Mix Sales =
VAR _allTimeAvgPrice =
    CALCULATE(
        DIVIDE(SUM('Data'[Sales]), SUM('Data'[Volume])),
        ALL('Date')
    )
RETURN
    SUMX (
        'Data',
        _allTimeAvgPrice * 'Data'[Volume]
    )

     _allTimeAvgPrice to give you the average calculated for the full dataset, and SUMX to iterate the every row of your Data table.

     

    Hope it works:)