Hi PUG Dublin
Thank you for sharing this opportunity with all of us. I would love to go to Amsterdam and meet the Microsoft product team .
I thought this would the perfect opportunity to make my first knowledge share with the group and hopeful help someone out there :)
Please see below my latest dashboard/reporting win:
Pareto chart, how to resolve problem with ties and measures
The Pareto chart is a handy visual, but is not so easy to build in either excel or Power BI, one of the most common issues you will come across when building your Pareto chart is flat lines.
This will happen if your data have the same figure when ranking using a measure.
In a Pareto chart, information is provided about an individual product or category as a bar, and a cumulative scale as a line which comprises all bars.
If you have tie figures in your data instead of a nice flowy line, you will end up with something like this:
In this example the client wants to analyse the production stoppage time by reason, being able to slice the chart by category.

As you can see right away our data have tie values for stop time (11,11;10,10).
What we would usually do:
Step #1-Create [Stop Time] measure
Stop Time := SUM('Stoppage Time'[Stop Time Hours])
Step #2 -Ranking reason based on [Stop Time]. As we want to be able to filter chart based on a slice, we nested it with ALLSELECTED
Rank1 :=
RANKX ( ALLSELECTED ( 'Stoppage Time' ), [Stop Time],, DESC, SKIP )
-- Ranking based on stop time
Step #3- Calculate cumulative value using TOPN for ALL table
Cumulative1 :=
CALCULATE ( [Stop Time], TOPN ( [Rank1], ALL ( 'Stoppage Time'[Reason] ) ) )
Step #4- Cumulative Percentage
Pct Cumulative1 :=
DIVIDE ( [Cumulative1], [Grand Total1] )
The outcome of the modelling that we just did, will have a number of issues, there are tied values for 2nd and 4th place, ranks are applied to Total and when plotting it to our chart, flat line will appear. In addition slicer won't work as expected.
OK, so now let's sort this out:
Step #1- In Power Query sort [Stop Time] in Descending order
Step #2- Add an Index column , this will solve the tied values problem
Step #3- Create a measure to rank the index column created above, that will be used as the basis for our next step.
Index Rank :=
RANKX (
FILTER (
ALL ( 'Stoppage Time' ),
-- table to rank
'Stoppage Time'[index] <= MAX ( 'Stoppage Time'[index] )
),
LASTNONBLANK ( 'Stoppage Time'[Index], 1 ),
,
ASC
)
Step #4- Calculate the Ranking using the new [Index Rank] measure
Rank2 :=
IF (
HASONEVALUE ( 'Stoppage Time'[Reason] ), -- remove values from totals
RANKX ( ALLSELECTED ( 'Stoppage Time' ), [Index Rank],, ASC, DENSE )
)
* note use HASONEVALUE to not show values on totals
Step #5- Calculate cumulative values using the index rank
Cumulative2 :=
CALCULATE (
[Stop Time],
TOPN ( [Index Rank], ALLSELECTED ( 'Stoppage Time'[Reason] ) )
Step #6- Calculate the Grand total and Cumulative %
Grand Total2 := CALCULATE([Stop Time],ALLSELECTED('Stoppage Time'))
*Note: Calculates Grand Total for All Entries Selected
Pct Cumulative2 :=
DIVIDE([Cumulative2],[Grand Total2])
And ta-dah, a beautiful flowy line standing tall with price in your chart. :)


and it works perfectly with the slicers too.
Surely there are other ways to make it work and this solution may not work for all scenarios, but it worked for me :)
The sharing of knowledge is power.
#Create #Develop #Model

------------------------------
Cecilia Pereira
@cissagmr
------------------------------
Original Message:
Sent: 02-16-2019 06:26 PM
From: Jose Almeida
Subject: Subject: Looking for 1:1 time with the Microsoft Product Team?
Did you know the Microsoft Product Team will be at Power Platform Summit Europe in full force presenting the Microsoft Power Series track – with over 15 hours of deep dive, hands on content – and the PowerBIUG is giving away one free personalized Power Pack?!
The Power Pack includes a free event pass to 2019 Power Platform Summit Europe, a Power BI t-shirt, User Group stickers, AND 30 minutes one-on-one with the Microsoft Power Series presenter of your choice. Ask questions, get ideas for your latest project, and give feedback to shape the future of Power BI.

To be entered to win please reply to this group post with your latest Power BI challenge, a recent dashboard/reporting win, or a Power BI enhancement idea. The winner will be chosen from the local User Group with the most discussion posts. The local winning PUG will also receive a feature on the PowerBIUG home page, a blog highlighting our plans for 2019, and social buzz.
The Microsoft Power Series will include sessions such as:
- AI in Power BI Service: Data Flow and Machine Learning
- Debugging common DAX and Model performance issues
- Advanced Data Visualization & Storytelling with Power BI Desktop
- And so much more – view the entire lineup here!
Register today with this 10% off UG coupon (Coupon Code: EUPSummit10) and take advantage of advanced registration savings of €200 ending February 28.
------------------------------
Jose Almeida
Data Analytics & Reporting Consultant
bordalos.com
------------------------------