Decile Ranking and grouping question

Power Query Data Setup
6

@jerry,

Is this the end result you’re looking for?

image

If so, I’ve fixed your measures per the solution file attached to calculate them properly per above. However, I had a hard time figuring out what you are trying to do with the calculated columns in your fact table. For this sort of analysis, you should use measures rather than calculated columns for a couple of reasons:

  1. doing rankings and decile calculations on a row by row basis doesn’t make logical sense, since you need to aggregate transactions by client before ranking or calculating deciles

  2. for these sorts of calculations, you typically want them to be wholly dynamic, so that you can slice by date, customer, etc. and still have the correct results display in your visualizations. That won’t be possible using calculated columns, since those only recalculate upon load or refresh.

If I’ve misinterpreted your requirement, please let me know and we can make the necessary changes to the solution.

I hope this is helpful. Full solution file attached below.