Forum Discussion
Creating cumulative measure based on dynamic ranking / Dynamic running total/pareto diagram-measure
Hi, try reviewing this blog's post.
Is in spanish but i hope can help you.
Regards
Victor
Lima - Peru
Hi,
You may refer to my solution in this workbook. There is one problem that when i select 2015 in the slicer, i get the result for "B" as 2 whereas it should be 3. I cannot figure out why that is happening. Hope someone else can pitch in.
Hi Vvelarde
A quick update:
I was able to get the measure to work, and it works great! Thanks. However, I am not able to make a table counting distinct number of suppliers of each category of accumulated spend (so called ABC-analysis)
Please see the picture below to see the updated results. As marked in the red circle, the table does not understand that I want to see distinct number of suppliers in each category. Any ideas of how to remedy this problem?
The formula for the categorization is
Supplier Category = IF([Accumulated spend per supplier]/[Total spend]<=0,8;"A - 80% of spend";IF([Accumulated spend per supplier]/[Total spend]<=0,95;"B - 80-95% of spend";"C - 95-100% of spend"))
As you can see the formula works great when I list all suppliers in the table to the left, but not in the table to the right. I have also uploaded the updated .pbix model here: https://www.dropbox.com/s/gviw1r3gmdm3ssx/Dummy1.pbix?dl=0
Any help is greatly appreciated!
/André
Hi,
You may refer to my solution in this workbook. There is one problem that when i select 2015 in the slicer, i get the result for "B" as 2 whereas it should be 3. I cannot figure out why that is happening. Hope someone else can pitch in.
Thanks for the reply and solution.
A little tweak of the formula seems to solve the problem, I guess it has something to do with the && logic in countrows.
Changing the measure for B to this solved the issue for me:
B - 80-95% of spend = COUNTROWS(FILTER(VALUES(Sheet1[SupplierName]);[Accumulated spend per supplier]/[Total spend]<=0,95))-[A - 80% of spend]
However, why is it not possible to select and slice the data by choosing for example the A category and get the other visuals updated?
Thanks
/André
Hi,
The A,B and C categories are the result of a measure. Had they been values lying in a column, then you would have been able to slice and dice by clicking.
Hi Ashish_Mathur,
Thanks for the reply!
Is there no way of making the slice and dice option available?
I have a similar use case, can you please share the pbix in which the solution is implemented?
Thanks in advance
I do not have the file. Describe the question, share some data and show the expected result.
