Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Prasad1
Helper I
Helper I

XYZ Product classification analysis in Power BI

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

ProductsSalescoefficient variation Group
JanuaryFebruaryMarchAprilMayJuneJuly
product1706580687576737%X
product22042363728401828%Z
product33417262530182323%Y
product4182720101572137%Z
product54338377856404328%Z
product6251920730212833%Z
product71924273212101043%Z
product8605766687169779%X
product93230152732181035%Z
product102228181922252114%Y

 Any help is g

 

 

 

 

I am posting a simple

1 ACCEPTED SOLUTION

@Prasad1,

 

I have tested it on my local environment by using the sample data below.
Capture.PNG

 

We need to unpivot this data and then calculte CV.
Capture1.PNG

Results
Capture2.PNG

 

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]))

Capture3.PNG

 

Regards,

Charlie Liao

View solution in original post

5 REPLIES 5
Prasad1
Helper I
Helper I

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

@Prasad1,

 

I have tested it on my local environment by using the sample data below.
Capture.PNG

 

We need to unpivot this data and then calculte CV.
Capture1.PNG

Results
Capture2.PNG

 

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]))

Capture3.PNG

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.