- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Pareto Chart.. almost there just missing something...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

AaronRogers3

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-04-2018
07:19 AM

Hi All,

I am very close to achieving a successful pareto chart however i am missing something and it is causing a problem for me.

Here is my data:

What I am trying to achieve is a pareto chart to show the duration hours vs the room number.

The DAX for TotalAmount = SUM(ENGINEER_TABLE[DURATION_HOURS])

The DAX for Cumulative Percent = [DURATION_HOURS] / [TotalAmount].

Here is the graph at the minute:

As you can see it is calculating the percentage total correctly... however why is it not to 100 percent at the right hand side?

Please help, Thanks!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Sean

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-04-2018
01:10 PM

Give these 5 __ MEASURES__ a try!

1)TotalAmount MEASURE= CALCULATE ( SUM ( ENGINEER_TABLE[DURATION_HOURS] ), ALLEXCEPT ( ENGINEER_TABLE, ENGINEER_TABLE[ROOM_NUMBER] ) )2)OverallTotal MEASURE= CALCULATE ( [TotalAmount MEASURE], ALL ( ENGINEER_TABLE ) )3)Room Rank MEASURE= RANKX ( ALL ( ENGINEER_TABLE[ROOM_NUMBER] ), [TotalAmount MEASURE] )4)Pareto Value MEASURE= SUMX ( TOPN ( [Room Rank MEASURE], ALL ( ENGINEER_TABLE[ROOM_NUMBER] ), [TotalAmount MEASURE] ), [TotalAmount MEASURE] )5)Pareto % MEASURE= DIVIDE ( [Pareto Value MEASURE], [OverallTotal MEASURE], 0 )

Here's the result!

Good Luck!

5 REPLIES 5

Greg_Deckler

Super User

Re: Pareto Chart.. almost there just missing something!?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-04-2018
09:20 AM

The issue is that your formula for cumulative percent is not a cumulative formula but instead just calculating the % of the current "bucket" (room #) in your case. The bigger issue is that generally a cumulative formula is based upon something like Date where you can filter out all dates less than the current date and use that for your accumulation. Not sure that will work with your room #'s.

But, in general a cumulative version of your formula would look something like this:

Cumulative Percent = VAR myRoom = MAX(Table[Room #]) CALCULATE([DURATION_HOURS],FITER(ALL(Table),[Room #] < myRoom)) / CALCULATE([TotalAmount],FILTER(ALL(Table),[Room #] < myRooom))

Proud to be a Datanaut!

AaronRogers3

Regular Visitor

Re: Pareto Chart.. almost there just missing something!?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-04-2018
09:33 AM

Hi Greg,

Appreciate the reply!

I copied and pasted your DAX and edited it to add in my corresponding field names - Like so:

Cumulative Percent =

VAR myRoom = MAX(ENGINEER_TABLE[ROOM_NUMBER])

CALCULATE([DURATION_HOURS],FILTER(ALL(ENGINEER_TABLE),[ROOM_NUMBER] < myRoom)) / CALCULATE([TotalAmount],FILTER(ALL(ENGINEER_TABLE),[ROOM_NUMBER] < myRoom))

However, I am receiving this error:

The syntax for 'CALCULATE' is incorrect. (DAX(VAR myRoom = MAX(ENGINEER_TABLE[ROOM_NUMBER])CALCULATE([DURATION_HOURS],FILTER(ALL(ENGINEER_TABLE),[ROOM_NUMBER] < myRoom)) / CALCULATE([TotalAmount],FILTER(ALL(ENGINEER_TABLE),[ROOM_NUMBER] < myRoom)))).

With red line under the first calculate, Total Amount and the final myRoom.

Any ideas?

Thanks!

Greg_Deckler

Super User

Re: Pareto Chart.. almost there just missing something!?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-04-2018
10:03 AM

Try this:

Cumulative Percent = VAR myRoom = MAX(ENGINEER_TABLE[ROOM_NUMBER]) CALCULATE(SUM([DURATION_HOURS]),FILTER(ALL(ENGINEER_TABLE),[ROOM_NUMBER] < myRoom)) / CALCULATE([TotalAmount],FILTER(ALL(ENGINEER_TABLE),[ROOM_NUMBER] < myRoom))

Proud to be a Datanaut!

Sean

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-04-2018
01:10 PM

Give these 5 __ MEASURES__ a try!

1)TotalAmount MEASURE= CALCULATE ( SUM ( ENGINEER_TABLE[DURATION_HOURS] ), ALLEXCEPT ( ENGINEER_TABLE, ENGINEER_TABLE[ROOM_NUMBER] ) )2)OverallTotal MEASURE= CALCULATE ( [TotalAmount MEASURE], ALL ( ENGINEER_TABLE ) )3)Room Rank MEASURE= RANKX ( ALL ( ENGINEER_TABLE[ROOM_NUMBER] ), [TotalAmount MEASURE] )4)Pareto Value MEASURE= SUMX ( TOPN ( [Room Rank MEASURE], ALL ( ENGINEER_TABLE[ROOM_NUMBER] ), [TotalAmount MEASURE] ), [TotalAmount MEASURE] )5)Pareto % MEASURE= DIVIDE ( [Pareto Value MEASURE], [OverallTotal MEASURE], 0 )

Here's the result!

Good Luck!

AaronRogers3

Regular Visitor

Re: Pareto Chart.. almost there just missing something!?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-05-2018
01:16 AM

Yes! absolute legend cheers Sean