none
Calculate Average Price of an accumulated RRS feed

  • Question

  • Hello everyone
    Thank you in advance for the help you can give me with the next topic
    I have the following situation ... I have some data in a power pivot table where I need to obtain the average of prices for a group of accumulated or grouped periods ... when creating the dynamic table and showing the data period by period, it shows me the correct data and as I show in the image when calculating the average of those prices gives me the value that I require, but that average value of the prices in green in the image and that I am calculating manually is the value that I need to show when grouping the same periods in the dynamic table ... I do not know what is missing in the DAX expression that I am using "Average Price: = DIVIDE ([Sales $], [Sales Units])" to be able to obtain this result

    again, thank you very much for your help and time

    regards

    Edwin

    
    Saturday, May 19, 2018 4:10 AM

Answers

  • Hi Edwin,

    Thanks for the sample data.

    >>>I do not know if I am asking too much I want a single expression to take into account this case and if I rather have to create two formulas but the ideal would be that the same expression would work.
    In this scenario, please try to use below DAX formula:

    AVGPrice:=
    AVERAGEX(values(RETAILERS[PER_LDESC]),DIVIDE([Sales Value],[Sales Unit]))


    Everything works as expected with your sample data:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 23, 2018 1:00 AM
    Moderator

All replies

  • Try this. Sumx(table,[average price] * [sales units]/calculate([sales units],all(table[date]))) I can’t give a more precise formula without seeing the data model Read about sumx in my article here https://exceleratorbi.com.au/use-sum-vs-sumx/

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Saturday, May 19, 2018 11:30 PM
    Answerer
  • Hi Edwin,

    Thanks for your question.

    In this scenario, please try below DAX formula:

    TotalPrice = sumx(ALLSELECTED(Sales), DIVIDE(Sales[Sales],Sales[Unit]))
    
    Average = DIVIDE([TotalPrice],COUNTROWS(ALLSELECTED(Sales[Date])))
    
    FinalAverage = DIVIDE(sumx(ALLSELECTED(Sales), DIVIDE(Sales[Sales],Sales[Unit]))
                          ,COUNTROWS(ALLSELECTED(Sales[Date])))
    

    See below images tested in Power BI desktop:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Monday, May 21, 2018 12:49 PM
    Monday, May 21, 2018 5:45 AM
    Moderator
  • A thousand thanks Willson for your answer,
    I confess that I was impressed with how you got the data, but I have not been able to solve it since I did not know how to explain it very well and you have to take into account some other variables ... try to modify your expressions but without any result.

    I will try to explain myself a little better

    in the following image attached the values that should have the value of the accumulated periods, which may change according to the selection of users, as you can see the average should be as shown in the formula of "AVERAGE" bone should take into account the individual value of the prices of each period before calculating the total accumulated average

    the idea of the DAX expression is that it allows me to see the correct value period by period or if in that case I select any accumulated one, show me the value taking into account the average of each period that composes it ... I do not know if I am asking too much I want a single expression to take into account this case and if I rather have to create two formulas but the ideal would be that the same expression would work

    I appreciate any help you can give me,
    I attached data with the example

    https://1drv.ms/x/s!AlCe4xaaThIxkF59v5HR9coL4Ddb


    regards
    Edwin


    Tuesday, May 22, 2018 1:16 AM
  • Hi Edwin,

    Thanks for the sample data.

    >>>I do not know if I am asking too much I want a single expression to take into account this case and if I rather have to create two formulas but the ideal would be that the same expression would work.
    In this scenario, please try to use below DAX formula:

    AVGPrice:=
    AVERAGEX(values(RETAILERS[PER_LDESC]),DIVIDE([Sales Value],[Sales Unit]))


    Everything works as expected with your sample data:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 23, 2018 1:00 AM
    Moderator
  • Thank you very much Willson,

    this was what I was looking for, it worked perfectly! thanks for your time and help

    regards
    Edwin

    Wednesday, May 23, 2018 10:40 AM