Help needed with pivot based on dynamic categories for ABC analysis

[PC2790]

Hi, I have a scenario as part of which I want to create a pivot in Power BI based on Categories-A,B,C and D from Stock & Consumption.

The calculation of A,B,C and D is as follows:

1)Total Stock which is a measure: Stock Value = 'Stock'[Stock_Qty] * 'Stock'[Net Price]

2) Calculate Cumulative stock value based on ranking in descending order starting from maximum stock value calculated in step 1(my problem lies here)

3) Stock% = Cumulative stock Value/ Total Stock(should be based on filters applied)

4)

ABCD Category =

SWITCH(TRUE(),

[Stock% ] < 0.7 || [ADC %] = 0.7 ,"A",

[Stock%] < 0.85 || [ADC %] = 0.85 ,"B",

[Stock%] < 0.95 || [ADC %] = 0.95 , "C",

[Stock%] < 1.00 || [ADC %] = 1.00 , "D",

0)

Now the problem here is that the cumulative stock value is to be calculated on the basis of ranking in descending order. And the ranking should be dynamic and should change based on the filters applied(filter are page level filters). I used Rankx function based on Item ID form my Stock table, but that will only work if my ITEM ID is in cope of the visual, what if the end user, removes item id and adds region to it or Type to it. The rank will not work and hence the subsequent steps as well. 

Is there any way to create a pivot that can have A,B,C and D categories of stock and A,B,C and D categories of stock as rows and columns and based on that the values extracted based on stock value and stock consumption can be provided in that pivot.

Something shown below:

providing the sample data  and the calculations expected for reference

@Ashish_Mathur @amitchandak  Will you be able to provide some assistance here please?

[Ashish_Mathur]

Hi,

I have not been able to make much headway.  In the SQV rank column, you will notice that the rank repeats because the SQV is the same.  Now when we calculate a running total, the value in rows 2 and 3 will be 234.6+179.2+179.2 whereas it should be 234.6+179.2 in row 2 and 234.6+179.2+179.2 in row 3.  I do not know how to do that in a measure.

[Ashish_Mathur]

Hi,

In that MS Excel file, create another tab which clearly shows the expected result.  Write formulas/create Pivot Tables and give descriptions to explain how you arrived at the expected result.

[PC2790]

@Ashish_Mathur I have edited the question and added all the required information in the attached excel. Please let me know if any further details are rquired

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.