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.
The calculation of A,B,C and D is as follows:
1)Total Stock which is a measure: Stock Value = 'Table'[Stock_Qty] * 'Table'[Net Price]
2) Calculate Cumulative stock value based on ranking(my problem lies here)
2) Stock% = Cumulative stock Value/ Total Stock(should be based on filters applied)
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 changes based on the filters applied.
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 get the rank dynamically based on which the stock% and category is selected?
