Forum Discussion

hidenseek9's avatar
hidenseek9
Post Patron
8 years ago
Solved

Calculating average price using distict count??

Hello Power BI Community

 

I am having a difficulty calculating average price of all of our products per store per month.

Please find below as the data.

Dummy Data

 

What I want to do is simple.

I would like to calculate an average price of each product for each brand by store.

Also is there a visual to show an average price as well as the lowest and highest price of the average??

(Like Stock Price Ticker?)

 

I am having a difficult time because there are multiple store price checks within a month,

so calculating a simple average just will not do it.

 

Your help is much appreciated.

 

Many thanks,

 

Hide

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi hidenseek9,

     

    Try these formulas

     

    Count=DISTINCTCOUNT(Data[Store])

    Contribution (%)=[Count]/CALCULATE([Count],ALL(Data[Brand]),ALL(Data[Product]))

     

    Hope this helps.

     

14 Replies

  • hidenseek9's avatar
    hidenseek9
    Post Patron
    8 years ago

    Hello Power BI Community

     

    I am having a difficulty calculating average price of all of our products per store per month.

    Please find below as the data.

    Sample Data

     

    What I want to do is simple.

    I would like to calculate an average price of each product for each brand by store.

    Also is there a visual to show an average price as well as the lowest and highest price of the average??

    (Like Stock Price Ticker?)

     

    Your help is much appreciated.

     

    Many thanks,

     

    Hide

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi hidenseek9,

     

    >>I would like to calculate an average price of each product for each brand by store.

    For this requirement, you can direct use matrix with original column to achieve this.

     

    Sample:

    Brand, Product Name as 'Rows', Stored as 'Columns', Total as Values with summary mode average.

     

    >>Also is there a visual to show an average price as well as the lowest and highest price of the average??

    You need to write a calculated column to calculate grouped average value, then create a matrix visual with above category and new calculated column.

    AVERAGE = 
VAR temp =
    SUMMARIZE (
        ALL ( 'Sample Data' ),
        [Brand],
        [Product Name],
        [Customer Danone Sales Team Name],
        "Average", AVERAGE ( [Price] )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            temp,
            [Brand] = EARLIER ( 'Sample Data'[Brand] )
                && [Product Name] = EARLIER ( 'Sample Data'[Product Name] )
                && [Customer Danone Sales Team Name]
                    = EARLIER ( [Customer Danone Sales Team Name] )
        ),
        "AVG", [Average]
    )

    Result visual: category to rows, average to values with summary mode maximum and minimum

     

    Summary.

    Expand.

     

    Regards,

    Xiaoxin Sheng

    • hidenseek9's avatar
      hidenseek9
      Post Patron
      8 years ago

      Anonymous

       

      Thank you for your reply.

      I am having a trouble with the DAX formula you provided on AVERAGE.

       

      when I type VAR as a DAX function, below four comes up.

      VAR.P

      VAR.S

      VARX.P

      VARX.S

      which one do I use to run the DAX formula you provided?

       

      And what would the new formula look like?

       

      Many thanks,

       

      H

       

       

       

       

      • hidenseek9's avatar
        hidenseek9
        Post Patron
        8 years ago

        Anonymous

         

        I am having a trouble with your solution.

        Which VAR DAX should I select?

         

        Or any other way to solve the issue?

         

        Many thanks,

         

        H

  • hidenseek9's avatar
    hidenseek9
    Post Patron
    8 years ago

    Hello Power BI Community,

     

    I have a question regarding understanding distribution of a product at store level.

    Please find below as a sample data

    Sample Data

     

    Data has a list of stores that sell certain products and our employees go to a store to check the price on shelf.

    Sometimes, the price check happens multiple times a month.

     

    What I would like to understand is that at any given month,

    in how many stores each product is sold.

    That way, I can understand the distirbution trend of our product over time.

     

    Appreciate your support.

     

    Many thanks,

     

    H