Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I'm trying to show a Pareto chart but I have serveral duplicated values; this results in a jagged step function, as PBI is rolling up the multiple occurances as one. For example:
Any ideas to show this properly? For example instead of 9%, 25%, 25% this should show 9%, 17%, 25%.
My cumulative percent function is just rolling up the Score by Business Risk:
This one can be done with a two column rank using a pattern like this.
First make a calculated column to get a rank based on the Risk category with an expression like this. It will give you rank your categories alphabetically.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks Pat, but it's returning 100% across the board. I realized that I didn't mention that the dataset is one level below "Business Risk" with a subcategory called "Vulnerabily" so I wonder if this is part of the issue, i.e. my raw data looks like this:
Business Risk | Vulnerability | ScoreCol | Score Measure | Business Risk Cumulative | Overall Total | Cumulative Risk Pct |
Customer Order Submission failure | Denial of Service/Network failure | 10 | 10 | 12% | 2174 | 12% |
PII Disclosure | Social Engineering/Phishing | 16 | 16 | 13% | 2174 | 13% |
Order fulfilment stoppage | Ransomware/Malware | 15 | 15 | 17% | 2174 | 8% |
Order Processing stoppage | Ransomware/Malware | 15 | 15 | 17% | 2174 | 17% |
Interestingly in my chart I'm getting 100% when I look at Business Risk alone (it's the dark blue line, the light blue is my original metric):
However if I filter on any aspect of Vulnerability your metric works perfectly! Is there something I can adjust so it will work at both levels?
Thanks,
Gregg
Hi @greggbb ,
Try the following formula:
Order =
VAR RoundedSales = [Score Measure]
RETURN IF (
HASONEVALUE ( JoinMatrix[Business Risk] ) && ( RoundedSales > 0 ),
VAR CustomersWithRankedName =
ADDCOLUMNS (
ALLSELECTED ( JoinMatrix ),
"@NameRanked", RANKX ( ALLSELECTED ( JoinMatrix ), JoinMatrix[Business Risk],, DESC, DENSE )
)
VAR MaxCustomerNameRanked =
MAXX ( CustomersWithRankedName, [@NameRanked] )
VAR LookupTable =
ADDCOLUMNS (
CustomersWithRankedName,
"@CustomerSales",
[Score Measure] * MaxCustomerNameRanked + [@NameRanked]
)
VAR CurrentName =
SELECTEDVALUE ( JoinMatrix[Business Risk] )
VAR CurrentNameRanked =
RANKX ( ALLSELECTED ( JoinMatrix ), JoinMatrix[Business Risk], CurrentName, DESC, DENSE )
VAR CurrentValue = RoundedSales * MaxCustomerNameRanked + CurrentNameRanked
VAR Ranking =
RANKX ( LookupTable, [@CustomerSales], CurrentValue,, DENSE )
RETURN
Ranking
)
Business Risk Cumulative =
var thisProduct = [Score Measure]
var allProducts = calculate( [Score Measure], all(JoinMatrix[Business Risk] ) )
var maxCurrentRank = [Order]
var rankTable = FILTER( ALLSELECTED(JoinMatrix[Business Risk]), [Order] <= maxCurrentRank)
return CALCULATE([Score Measure], rankTable) / allProducts
Referencing: https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Winniz, but that isn't working either. In fact it's all coming in at 98%:
This has become oddly complicated for something that I can easily build in Excel. I'm going to look for a different visualization to get my point across as it doesn't seem Power BI can handle the math when duplication is involved.
Thanks,
Gregg
Hi @greggbb ,
Could you please share your PBIX file without sensitive data? In my test data, it works well.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
123 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
90 | |
67 | |
62 | |
53 |