Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
greggbb
Frequent Visitor

Pareto with duplicate values

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:

 

greggbb_0-1629929101165.png

 

greggbb_1-1629929167421.png

 

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:

Business Risk Cumulative =
var thisProduct=[Score Measure]
var allProducts=calculate([Score Measure],all(JoinMatrix[Business Risk]))
return
calculate([Score Measure],filter(all(JoinMatrix[Business Risk]),[Score Measure]>=thisProduct))/allProducts
5 REPLIES 5
mahoneypat
Employee
Employee

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.

 

ForRiskRank = var thisvalue = Risk[Risk] return RANKX(Risk, Risk[Risk], thisvalue,DESC)
 
Then create a measure like this (replace Risk with your actual table name, and correct column/measure references).  The alphabetical rank is divided by 1000 so that result can't affect the overall ranking based on risk.  Adapt as needed.
 
Cumulative Risk Pct =
VAR thisvalue =
    MIN ( Risk[Value] )
VAR thisriskrank =
    MIN ( Risk[ForRiskRank] ) / 1000
VAR summary =
    ADDCOLUMNS (
        ALLSELECTED ( Risk[Risk] ),
        "cValue"CALCULATE ( MIN ( Risk[Value] ) ),
        "cRanked",
            CALCULATE ( SUMX ( Risk, Risk[Value] + Risk[ForRiskRank] / 1000 ) )
    )
VAR totalvalue =
    SUMX ( summary, [cValue] )
VAR cumulativevalue =
    SUMX ( FILTER ( summary, [cRanked] >= thisvalue + thisriskrank ), [cValue] )
RETURN
    DIVIDE ( cumulativevaluetotalvalue )
 
 
I made a simple table to test it out below.
 
mahoneypat_0-1629933574850.png

 

 
Pat
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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 RiskVulnerabilityScoreColScore MeasureBusiness Risk CumulativeOverall TotalCumulative Risk Pct
Customer Order Submission failureDenial of Service/Network failure101012%217412%
PII DisclosureSocial Engineering/Phishing161613%217413%
Order fulfilment stoppageRansomware/Malware151517%21748%
Order Processing stoppageRansomware/Malware151517%217417%

 

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):

greggbb_1-1629980122013.png

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

 

 image.png

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%:

 

greggbb_0-1630418794476.png

 

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.

 

vkkfmsft_0-1630651730005.png

tempsniptst.png

 

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.