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.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    tfr111
    Frequent Visitor
    1 year ago

    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's avatar
      HarishKM
      Super User
      1 year ago

      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's avatar
        tfr111
        Frequent Visitor
        1 year ago

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