Forum Discussion

Prasad1's avatar
Prasad1
Icon for Helper I rankHelper I
9 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 us...
Tutorial Requests
  • v-caliao-msft's avatar
    v-caliao-msft
    9 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

Prasad1's avatar
Prasad1
Icon for Helper I rankHelper I
9 years ago

Any help is grately appreciated. Thanks

Prasad

Prasad1's avatar
Prasad1
Icon for Helper I rankHelper I
9 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
    9 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

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

      Hi Charlie Liao

       

      Brilliant solution. Thank you very much . Worked nicely.

       

      Tryly appreciate your help.

       

      Regards

      Prasad

    • krzysztofpiotro's avatar
      krzysztofpiotro
      Frequent Visitor
      8 years ago

      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?