Calculating average price using distict count??

[hidenseek9]

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

[hidenseek9]

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

[Ashish_Mathur]

Hi,

I get a message saying that the file size exceeds 5MB.  Thereafter there is no option to download.

[hidenseek9]

@Ashish_Mathur

I have updated the file.

Sample Data

Please check.

Many thanks,

H

[Ashish_Mathur]

Hi @hidenseek9,

You say that you want to know in how many stores each product is sold?  In your dataset, i see Product Name but i do not see any Store Name column.  Please show the exact structure of your expected result.

[hidenseek9]

@Ashish_Mathur

Tempo data in column A is the store name

Appreciate your help

Many thanks

Hide

[Ashish_Mathur]

Hi @hidenseek9,

Please share the exact structure you are expecting in the final result.

[hidenseek9]

@Ashish_Mathur

What I would like to know is below on any given month.

1. A total number of stores checked. (i.e. Brand A: 200 stores, Brand B: 300 stores,...All Brand 1000 stores)
2. A total number of stores that each product is sold. (i.e. Brand A Product Name 1: 50 stores, Brand A Product Name 2: 100 stores, etc.)
3. Distribution of each product in percentage. (This would be 2 / 1 (A total number of stores checked for all brands)

Many thanks,

H

[Ashish_Mathur]

Hi,

When i try to build a Pivot Table, i get the same number of stored checked for all brands.  There is something wrong with the dummy data that you have shared.  Share a smaller dataset and with respect to that smaller dataset, share the expected result (show actual numbers that you are expecting there).

[hidenseek9]

@Ashish_Mathur

Here is a simplified version of the sample data.

I have also attached the answers to the questions in the file already.

Sample Data

Basically, I would like to calculate the following in PowerBI

• Air Jordan Distribution (Brand)
• Air Jordan 1 Distribution
• Air Jordan 2 Distribution
• Carmelo Distribution (Brand)
• Carmelo 1 Distribution
• Carmelo 2 Distribution

Many thanks,

H

[Ashish_Mathur · Accepted solution]

Hi @hidenseek9,

Try these formulas

Count=DISTINCTCOUNT(Data[Store])

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

Hope this helps.

[v-shex-msft]

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]

@v-shex-msft

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]

@v-shex-msft

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]

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