Power BI - Conditionally format top/bottom cumulative percentage?
Looked around a long time now, and cannot find a way to conditionally format the values in a table, treemap, bar chart etc. if they are within the say, top 80% of all the values.
So let's say I have a table with sales and profit. Then I have a treemap showing the profit by city. I manage to get the percentage of total with:
% of total profit = SUM(Orders[Profit])/CALCULATE(SUM(Orders[Profit]),ALL(Orders))
..so that is no issue. But how would I go about formatting the cities that are within the top 80% of all profit? Any suggestion on approach?
Example:
Say I have the treemap below. The first four categories would fall within the top 80% of the total. Cumulatively it would be: 35%, 55%, 70% and 85%. So my need is to color only these in a specific way (and preferably any that falls outside of that range, in another color).
I take it that finding the logic to do this, likely though a measure somehow to base the formatting on, will then also be something that would be applicable to bar charts etc. (more traditional pareto).
OK, here is the solution. I will provide a traditional pareto and an adapted one according to your requirements. My data looks like this.
Create 3 measure as follows:
1
Profit Measure = SUM('Table'[Profit])
2
Pareto =
VAR amount = [Profit Measure]
VAR total = CALCULATE( [Profit Measure], REMOVEFILTERS('Table'))
VAR result = CALCULATE([Profit Measure], FILTER(ALL('Table'),[Profit Measure]>= amount))/total
RETURN result
3
Threshold =
VAR pareto = [Pareto]
VAR previousPareto = MAXX( FILTER( SUMMARIZE(ALL('Table'),'Table'[Order],'Table'[Profit],"p", [Pareto]), [p] < pareto), [Pareto])
RETURN IF( ISBLANK( previousPareto), pareto, previousPareto)
Create a table visual so you can see what is happening. In the table below, the Pareto measure is creating a cumulative percentage based on profit. If you were to use this, you can see that only A and B are cumulatively the only values actually < 80% and so they would be the only ones coloured. What you are asking for is slightly different which is what the threshold measure is showing. This outputs the previous pareto (or current if there is no previous).
In the tree map, I have created the conditional formatting as follows for standard pareto.
And as follows for the adapted pareto.
