Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Community -
I have a data set where I am tracking the number of late lines, per product. And doing a pareto of these items. A number of items have the same amount of late lines. Meaning, the order line the product was on shipped late. So for example, in the chart below you can see that a number of different items have the same amount of late shipped lines...resulting in a flat line where the ties occur. I guess my question is whether or not this is acceptable in the world of pareto charts? Or should a pareto Always have the traditional curve to it?
The conditional formatting (grey color) kicks in at the 80% value on the pareto blue line.
Solved! Go to Solution.
@Anonymous
It seems your data is not the same granularity as mine, I added a dimension table for the Products and repeated the fact table lines. Please the attached file. Created a new table and added new measures. Please check.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I was able to reproduce your solution, but the data zeroed out (which has values >1 in other months) is showing up on the graph. I use filters for different months and periods (quarter and semester) to generate periodic reports, and I intend to use this same base. How can I hide them?
@Anonymous
Ideally, in a Pareto chart, each value should get added and that should be calculated as a % of the total. I think you need to look at your calculation to avoid getting similar percentages when they the same.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Makes sense, but where you have repeated values, what I am getting is the same cumulative percent. For example. So you are saying I should have a different cumulative percent even if I have the same value for the late shipped lines?
# of late lines cumulative percent
6 33.85%
6 33.85%
6 33.85%
7 28.69%
7 28.69%
@Anonymous
When it comes to ties, you need to incorporate another column to break them, I did a sample file with Pareto using this technique. Please find attached the file below my signature. Product B and C have the same values but still adds up.
Pareto =
VAR __CurrentRank =
RANKX (
ALL ( Table1[Product] ),
CALCULATE ( [Total Sales] + UNICODE ( MIN ( Table1[Product] ) ) / 1000 ), , ,
DENSE
)
VAR __Total =
CALCULATE ( [Total Sales], ALLSELECTED () )
VAR __Cummulative =
SUMX (
FILTER (
ADDCOLUMNS (
ALLSELECTED ( Table1[Product] ),
"rank",
RANKX (
ALL ( Table1[Product] ),
CALCULATE ( [Total Sales] + UNICODE ( MIN ( Table1[Product] ) ) / 1000 ), ,,
DENSE
)
),
[rank] <= __CurrentRank
),
[Total Sales]
)
RETURN
DIVIDE ( __Cummulative, __Total )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I would just add that it seems wherever there is a tie, the pareto line dips down. As seen here:
Hi @Fowmy I applied your measure, and my results are a bit sporadic. Thank you for taking the time to help...just curious why I might be getting this erratic behavior. Here is the measure I used. Basically, your measure with my actual names included.
@Anonymous
Can you share a PBIX file with the above chart and sample data? You can replace actual numbers with dummy numbers.
Save the file in OneDrive or any other location and share the link.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy I am not at liberty to share the file, but I think I've at least discovered a pattern around where the pareto line is dipping down. I see that you are using Unicode in the measure and it is interesting that the "dips" on the line happen everywhere there is a similarity in the part number. For example, the "44" below is a three way tie. The first digit on the first "44" is a 7. However, on the other two the part numbers both start with 713...and you can see the percentage is the same for both. Likewise for the 43. Both part numbers in this case begin with "C". The measure works fine for ties where the part numbers do not have these similarities. Perhaps changing the "unicode" to something different would help? Just not sure how to go about this.
@Anonymous
I took just one letter in my product column that is the reason. I modified the measure and a new measure to rank the products:
ProductRank =
VAR __currentproduct =
SELECTEDVALUE ( Table1[Product] )
RETURN
DIVIDE (
COUNTROWS (
FILTER ( ALLSELECTED( Table1[Product] ), Table1[Product] <= __currentproduct )
),
100
)
Pareto Measure:
Pareto =
VAR __totalsales =
CALCULATE ( [Total Sales], ALLSELECTED () )
VAR __Curretnrank =
RANKX ( ALL ( Table1[Product] ), [Total Sales] + [ProductRank],, DESC )
RETURN
DIVIDE (
CALCULATE (
[Total Sales],
FILTER (
ADDCOLUMNS (
ALLSELECTED ( Table1[Product] ),
"R", RANKX ( ALL ( Table1[Product] ), [Total Sales] + [ProductRank],, DESC )
),
[R] <= __Curretnrank
)
),
__totalsales
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello @Fowmy Incorporating those latest measures, unfortunately still gives me strange results. Below I have sorted the pareto percentages. Normally, you would see this cumulatively increasing. But take a look at the rows with "44". One appears at the top, and two others with this same row count appear at the bottom. The top one has an item number starting with 715 and the other two are 1024 and 1023. It still seems that somehow the ranking is being affected by the numeric (or alphanumeric) values of the product.
@Anonymous
If you can build a dummy simple two column table in Excel representing the similar product patter as per your original data and share, I will be able to check
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy This would be representative of the data.
Item | Lines |
787525 | 10 |
787523B | 10 |
CA-767 | 45 |
CA-769 | 45 |
13457B | 19 |
12348 | 25 |
12348B | 31 |
@Anonymous
It works for me,
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy Thanks for your help. I have done everything I can to try and get my pareto to work. My formulas are exactly the same, except for I am doing a count of rows, versus a sum of sales. No matter what, I still get pareto percent values that repeat. I've got someone else looking at a solution and evaluating why my version is not working....will report back once I have that solution. But your solution definitely worked in your file...just not sure why it is not working in mine, which I hope to have an answer for.
@Anonymous
It seems your data is not the same granularity as mine, I added a dimension table for the Products and repeated the fact table lines. Please the attached file. Created a new table and added new measures. Please check.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
93 | |
83 | |
77 | |
74 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |