Removing date filter

[tfr111]

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 price (per transaction, month, year, etc.), so I can compare them (on total level, by customer, product, etc.)

I am using a data table with a date hierarchy.

My DAX expressions:

All time average price = 
DIVIDE(
    CALCULATE(SUM('Data'[Sales]), ALL('Data'[Date])),
    CALCULATE(SUM('Data'[Volume]), ALL('Data'[Date]))
)

Average price = 
DIVIDE(
    CALCULATE(SUM('Data'[Sales]),
    CALCULATE(SUM('Data'[Volume])
)

Both expression return the same values, i.e., the ALL('Data'[Date]) does not effectively shifting the calculation to fhe full timeframe.

What do I need to change?

The output for "all time average price" should have 48,78/62,25/62,06 in each month cell. 

Thank you,

Tom

[v-tsaipranay]

Hi @tfr111 ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[v-tsaipranay]

Hi @tfr111 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tsaipranay · Accepted solution]

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]

Dear @v-tsaipranay 

thank you very much for the proposal. It worked perfectly!

Best,

tfr

[v-tsaipranay]

Hi @tfr111 ,

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used sample data on my end and successfully implemented it.     
I am also including .pbix file for your better understanding, please have a look into it:

Thank you for using Microsoft Community Forum.

[tfr111]

Hi @v-tsaipranay 

thank you very much for taking the time to look into my problem.

We are one step closer: The calculation aggregates correctly for customer, but not for dates:

Any ideas how to fix this?

BTW, I have removed the REMOVEFILTERS('Data') from the following expression, as it should calculate across time, but not across customers.

Thank you very much!

[v-tsaipranay]

Hi @tfr111 ,

Thank you for reaching out to the Microsoft Fabric Community Forum. Thank you @Sandip_Palit  for your response.

The issue with "New Mix Sales" not summing correctly is because Power BI does not perform row-wise calculations at the total level unless specified.

As mentioned by @danextian , using a dedicated Date table is important for managing context, and applying the SUMX pattern suggested by @MasonMA will ensure totals are calculated correctly on a row-by-row basis.

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

This approach ensures your matrix total aligns with expectations. Hope this helps. Please reach out for further assistance.

Thank you.

[tfr111]

Thank you very much for the answers.

I tried this suggestion, but the calculation still returns the same output:

Any further ideas what to do?

Thank you

[HarishKM]

@tfr111 Hey,
Can you try below 2 dax.

Dax 1 : DAX Expression for All-Time Average Price 
AllTimeAveragePrice =
Var AT = DIVIDE( CALCULATE( SUM('Data'[Sales]), ALL('Data') ), CALCULATE( SUM('Data'[Volume]), ALL('Data') ) )

return
AT

Dax 2:  DAX for Average Price with Filter Context 

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

Tips :

1) Apply these measures to matrix visuals and ensure no additional filters are altering their visibility.

2) Cross-check your data model relationships to confirm the correctness of volume-related interactions.

Thanks

Harish KM

If these steps help resolve your issue, your acknowledgment would be greatly appreciated.

[tfr111]

Hi Harish,

many thanks for the suggestion. I tried but, did not get it to work.

I put together an example with dummy data where the problem is visible.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUTI1MDAAUoZg0sBQD4iMDIxMlGJ10BSaYVVoiqrQCChngUthLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Sales = _t, Volume = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Sales", Int64.Type}, {"Volume", Int64.Type}, {"Date", type date}, {"Product", type text}})
in
    #"Changed Type"

Date = 
ADDCOLUMNS (
    CALENDAR (DATE(2024, 1, 1), DATE(2026, 12, 31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Month Number", MONTH([Date]),
    "Day", DAY([Date]),
    "Weekday", FORMAT([Date], "dddd")
)

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

All-time Avg Price = 
DIVIDE(
CALCULATE(sum(Data[Sales]),all('Date')),
CALCULATE(sum(Data[Volume]),all('Date'))
)

Sales at all-time avg price = SUMX (
        Data,
        [All-time Avg Price] * Data[Volume]
    )

[danextian]

Hi @tfr111 

Use a dedicated dates table so you can apply the filter modifier to the entire date context. Right now, you're modifying the filter on the Data date column, but your visual uses different columns like month and year that the filter modifier isn't affecting. You're applying ALL to the date column, but not to the month and year columns, which is why it doesn't behave as expected.

[tfr111]

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]

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:) 

[MasonMA]

@tfr111 

Hi there,

Is your 'Data'[Date] in an actual Date table that filters your fact table? It does not look like so to me as your Sales and Volumes colums are all in this table. 

If they are all in the fact table, ALL('Data'[Date]) only removes filters from the Date column inside the fact table, not the filters from the external Date table.

Assuming your model has a Date table called 'Date' that is related to 'Data' (if there is not, you would need to build one for the purpose of better performance) you should write:

AllTimeAvgPrice =
DIVIDE(
CALCULATE(
SUM('Data'[Sales]),
ALL('Date') 
),
CALCULATE(
SUM('Data'[Volume]),
ALL('Date')
)
)

If you only has this one table in the model and you are not planning to make any model change, you may try with ALL('Data') as your filter modifier instead of ALL('Data'[Date])

Hope it helps:) 

[Sandip_Palit]

Here are the corrected formulas. First, let's simplify your base measure for the individual average price. Then, we'll create the "All time" version that correctly ignores the date filters.

1. Average Price (Current Period)

This simple measure will correctly calculate the average price based on the current context (the specific year, month, customer, etc. selected in your visual).

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

2. All-Time Average Price

This measure reuses the [Average Price] measure but modifies its context. It removes all filters from your Calendar table to get the true, all-time average.

All Time Average Price =
CALCULATE(
[Average Price],
ALL('Calendar')
)

By using this pattern, the [All Time Average Price] will remain constant across different time periods in your visual, allowing for a direct comparison, while still respecting filters from other tables (like Customers or Products).

If this explanation and solution resolve your issue, please like and accept the solution.