Forum Discussion

Prasad1's avatar
Prasad1
Icon for Helper I rankHelper I
8 years ago
Solved

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

Tutorial Requests
  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    Prasad1,

     

    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

5 Replies

    • Prasad1's avatar
      Prasad1
      Icon for Helper I rankHelper I
      8 years ago

      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

      • v-caliao-msft's avatar
        v-caliao-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Prasad1,

         

        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