Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
XYZ Analysis is a popular Product segmenation approach used for inventory analysis.
http://wikieducator.org/Xyz_analysis.
I would like to understand the way to carryout XYZ analysis using Power BI.
The steps are
1. For a given set of sales across months, sales avearge is to be calculated by taking sum of sales across all months / no of months that the sales posted (if there are 12 months, but sales are posted only for 10 mmonths and for two months, there is no sale, then the average should be taken as sum of 10 months sales / 10 (Not 12)
2. Co-effeicient of Variation is to be calculated as Co-efficient of Variaion CV = STDEVP(of all monthly sales columns)/AVERAGE of sales calcualted in step 1
3. Finally, if the CV = 10%, the product is segmented as X category, if CV is greater than 10% but less than 25%, then it is segmented as Y category and finally if CV is > 25% it is Z.
Insert photos function is not working correctly to post the image
Products | Sales | coefficient variation | Group | ||||||
January | February | March | April | May | June | July | |||
product1 | 70 | 65 | 80 | 68 | 75 | 76 | 73 | 7% | X |
product2 | 20 | 42 | 36 | 37 | 28 | 40 | 18 | 28% | Z |
product3 | 34 | 17 | 26 | 25 | 30 | 18 | 23 | 23% | Y |
product4 | 18 | 27 | 20 | 10 | 15 | 7 | 21 | 37% | Z |
product5 | 43 | 38 | 37 | 78 | 56 | 40 | 43 | 28% | Z |
product6 | 25 | 19 | 20 | 7 | 30 | 21 | 28 | 33% | Z |
product7 | 19 | 24 | 27 | 32 | 12 | 10 | 10 | 43% | Z |
product8 | 60 | 57 | 66 | 68 | 71 | 69 | 77 | 9% | X |
product9 | 32 | 30 | 15 | 27 | 32 | 18 | 10 | 35% | Z |
product10 | 22 | 28 | 18 | 19 | 22 | 25 | 21 | 14% | Y |
Any help is g
I am posting a simple
Solved! Go to Solution.
I have tested it on my local environment by using the sample data below.
We need to unpivot this data and then calculte CV.
Results
Then create a calculate colum to get the CV.
CV = CALCULATE(STDEV.P(Table1[Value]),ALLEXCEPT(Table1,Table1[Product]))/CALCULATE(AVERAGE(Table1[Value]),ALLEXCEPT(Table1,Table1[Product]))
Regards,
Charlie Liao
Any help is grately appreciated. Thanks
Prasad
Hi
Columns are displaced in the problem above. The Photo option to upload image is horrible. It is not working properly. Anyway, it has sales numbers from Jan to Jul. The next column with % values is co-coeffecient of variation and finally Group is XYZ product tag. Hope it is clear.
Thanks
Prasad
I have tested it on my local environment by using the sample data below.
We need to unpivot this data and then calculte CV.
Results
Then create a calculate colum to get the CV.
CV = CALCULATE(STDEV.P(Table1[Value]),ALLEXCEPT(Table1,Table1[Product]))/CALCULATE(AVERAGE(Table1[Value]),ALLEXCEPT(Table1,Table1[Product]))
Regards,
Charlie Liao
Hello,
Is this approach correct?
If no sales occured in April for Product 1, shouldn't it be zero instead of blank? Now we are counting CV for sales from 6 months, but in fact there are 7 month considered in analysis. Considering 0 for sales from April and counting for 7 months, CV = 0,5971 (for Product1) and this is a huge difference, comparing with the one calculated for 6 months (0,4035).
So, do you have any idea how to consider 0 for months without sales?
Hi Charlie Liao
Brilliant solution. Thank you very much . Worked nicely.
Tryly appreciate your help.
Regards
Prasad
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |