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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AaronRogers3
Helper I
Helper I

Pareto Chart.. almost there just missing something!?

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:

 

CapturePBI.PNG   

 

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:

 

CapturePBI2.PNG

 

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!

1 ACCEPTED SOLUTION

@AaronRogers3

 

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!

 

Pareto - 2018-04-04.png

 

Good Luck! Smiley Happy

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!Smiley Happy

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@AaronRogers3

 

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!

 

Pareto - 2018-04-04.png

 

Good Luck! Smiley Happy

Yes! absolute legend cheers Sean Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.