Problem calculating standard deviation of a measure

[michaelstone]

Hi all,

I'm trying to identify which products are the most frequently purchased by customers by using the standard deviation as a threshold.

I have two tables:

• A Products table which has a unique row for each distinct product I am selling
• A Transactions table which has a row for each item in a shopper's basket. This includes a reference to the Products table as well as a Transaction ID. Note that the same Transaction ID can have several rows i.e. the customer purchased a number of different products in the same transaction.

I have created a measure to calculate Frequency of Purchase i.e. what proportion of transactions include the product of interest as follows.

Frequency of Purchase = 
    DISTINCTCOUNT(Transactions[order_id])/CALCULATE(DISTINCTCOUNT(Transactions[TransactionID]),ALLEXCEPT('Products','Products'[ProductID]))

If I visualise this in a table along with the product name, I get the correct values for Frequency of Purchase.

The problem is with my measure to calculate the Standard Deviation of the Frequency of Purchase

Frequency StdDev = STDEVX.P('Products',[Frequency of Purchase])

The measure above always returns 0.

I am doing a similar thing to indentify the products with the best margin which involves calculating the Standard Deviation of a Margin measure. This works fine so not sure why Frequency is not working.

Any assistance will be appreciated!

Thanks,

Michael Stone

[v-frfei-msft]

Hi @michaelstone ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

Regards,

Frank

[michaelstone]

Hi @v-frfei-msft ,

Have you had a chance to look at the data file I uploaded or do you need more information?

Thanks,

Michael

[michaelstone]

Hi Frank,

Thanks for responding!

Below is a link to a PBIX file which illustrates the problem.

https://invigor.sharepoint.com/:u:/g/EWeDmEJTSx9LmwsHb-V9QgQB3vYzQlt5OvtRDvqCu7V58w?e=NteoDY

Basically I want to be able to dynamically classify products as High/Low Frequency and High/Low Margin where High is more than 1 standard deviation from the mean.

Frequency is calculated based on the proportion of transactions that the product appears in (transaction_id identifies a single "basket").

Margin is (sales-cost)/price expressed as a percentage.

The High/Low Margin is working correctly but the High/Low Frequency as affected by two issue:

• Standard deviation is 0
• Frequency of purchase is 100% for all products.

Hope this helps!

Thanks,

Michael Stone

[michaelstone]

Hi @v-frfei-msft ,

Probably should have tagged you on my reply above.

Any help you can provide will be appreciated!

Thanks,

Michael