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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
mol_ad
New Member

Cumulative Total

I am trying to create a Pareto Chart using Dax Formulas for the set of 5 metrics in a column (Error Column below)...I have created the following Dax Measures:

Error Volume = Data from the data source table
Running Total = 

VAR CurrentCount = [Error Volume]
RETURN
    CALCULATE([Error Volume],
    FILTER(
        ALL('Follow-Up Team'[Errors]),
        [Error Volume]>= CurrentCount
    ))
Chart % =
VAR ErrorCount = [Error Volume]
VAR AllErrors = CALCULATE([Error Volume],ALLSELECTED('Follow-Up Team'))
RETURN
DIVIDE(
SUMX(
    FILTER(
        SUMMARIZE(ALLSELECTED('Follow-Up Team'),'Follow-Up Team'[Errors],
            "Volume",'Follow-Up Team'[Error Volume]),
                [Volume] >= ErrorCount),
                    [Volume]),
    AllErrors,0)    

 

Below is the cooresponding table for a given filtered time period.

 

Errors          Error Volume          Running Total          Chart %

 #1                        9                              9                      26%

 #2                        8                             25                     71%
 #
3                        8                             25                     71%

 #4                        6                             31                     89%

 #5                        4                             35                    100%

 

I need the Running Total for Error #2 = 17 (9+8) and Chart % for Error #2 = 49%.

8 REPLIES 8
johnbasha33
Post Patron
Post Patron

@mol_ad 

Running Total =
VAR CurrentError = MAX('Follow-Up Team'[Errors])
RETURN
CALCULATE(
SUM('Follow-Up Team'[Error Volume]),
FILTER(
ALL('Follow-Up Team'[Errors]),
'Follow-Up Team'[Errors] <= CurrentError
)
)

When using this formula, I am still running into the same problem.  When the errors are sorted alphabetically it is working as expected.  However, I am not sorting alphabetically - I am sorting by error volume (see the following):

 

This is what I am looking for:

Error                                     E.V.      R.T.     Chart %

Timeliness111134.38%
Appropriate Notes112268.75%
Appropriate Escalation42681.25%
Appropriate Activity Code32990.63%
Resolution332100.00%

 

Here is the resulting table I get when sorting the Errors by Error Volume using your given formula:

ErrorsError VolumeRunning Total
Appropriate Notes1118
Timeliness1132
Appropriate Escalation47
Appropriate Activity Code33
Resolution321
Total32 

 

 

v-kongfanf-msft
Community Support
Community Support

Hi @mol_ad ,

 

May I ask what scenario you are testing? I used the solution provided by @Jihwan_Kim  and everything shows up fine.

vkongfanfmsft_0-1712211438508.png

 

Best Regards,
Adamk Kong

I believe I have found the reason I was getting the error.  I only provided information that I was using after a filter and not the entire data source table.  Since this is first time I am posting, I have not yet figured out how to attach a file, so I will provide a sample of the excel file data source.

*Note: I have provided 2 Users for a 3-Month sample, but I have a total 28 Users with more to be added in the future*

 

Unique IdentifierUserSupervisorMonthErrorsError VolumeEfficiency ScoreQuarter
T.B.-JanuaryT.B.Supervisor #1JanuaryTimeliness260%Q1
T.B.-JanuaryT.B.Supervisor #1JanuaryAppropriate Activity Code0100%Q1
T.B.-JanuaryT.B.Supervisor #1JanuaryAppropriate Notes180%Q1
T.B.-JanuaryT.B.Supervisor #1JanuaryAppropriate Escalation180%Q1
T.B.-JanuaryT.B.Supervisor #1JanuaryResolution0100%Q1
T.B.-FebruaryT.B.Supervisor #1FebruaryTimeliness260%Q1
T.B.-FebruaryT.B.Supervisor #1FebruaryAppropriate Activity Code260%Q1
T.B.-FebruaryT.B.Supervisor #1FebruaryAppropriate Notes340%Q1
T.B.-FebruaryT.B.Supervisor #1FebruaryAppropriate Escalation0100%Q1
T.B.-FebruaryT.B.Supervisor #1FebruaryResolution180%Q1
T.B.-MarchT.B.Supervisor #1MarchTimeliness340%Q1
T.B.-MarchT.B.Supervisor #1MarchAppropriate Activity Code0100%Q1
T.B.-MarchT.B.Supervisor #1MarchAppropriate Notes260%Q1
T.B.-MarchT.B.Supervisor #1MarchAppropriate Escalation0100%Q1
T.B.-MarchT.B.Supervisor #1MarchResolution180%Q1
L.B.-JanuaryL.B.Supervisor #2JanuaryTimeliness180%Q1
L.B.-JanuaryL.B.Supervisor #2JanuaryAppropriate Activity Code0100%Q1
L.B.-JanuaryL.B.Supervisor #2JanuaryAppropriate Notes260%Q1
L.B.-JanuaryL.B.Supervisor #2JanuaryAppropriate Escalation180%Q1
L.B.-JanuaryL.B.Supervisor #2JanuaryResolution0100%Q1
L.B.-FebruaryL.B.Supervisor #2FebruaryTimeliness340%Q1
L.B.-FebruaryL.B.Supervisor #2FebruaryAppropriate Activity Code0100%Q1
L.B.-FebruaryL.B.Supervisor #2FebruaryAppropriate Notes0100%Q1
L.B.-FebruaryL.B.Supervisor #2FebruaryAppropriate Escalation0100%Q1
L.B.-FebruaryL.B.Supervisor #2FebruaryResolution180%Q1
L.B.-MarchL.B.Supervisor #2MarchTimeliness0100%Q1
L.B.-MarchL.B.Supervisor #2MarchAppropriate Activity Code180%Q1
L.B.-MarchL.B.Supervisor #2MarchAppropriate Notes340%Q1
L.B.-MarchL.B.Supervisor #2MarchAppropriate Escalation260%Q1
L.B.-MarchL.B.Supervisor #2MarchResolution0100%Q1

 

This chart shows what I expect the table and therefore Pareto Chart to look like.

 

Error                                     E.V.      .T.     Chart %

Timeliness111134.38%
Appropriate Notes112268.75%
Appropriate Escalation42681.25%
Appropriate Activity Code32990.63%
Resolution332100.00%

Hi @mol_ad ,

 

You can try formula like below:

Running Total = 
VAR CurrentError =
    SELECTEDVALUE ( 'YourTableName'[Errors] )
RETURN
    CALCULATE (
        SUM(YourTableName[Error Volume]),
        FILTER (
            ALLSELECTED ( 'YourTableName'[Errors] ),
            'YourTableName'[Errors] <= CurrentError
        )
    )
ratio: =
DIVIDE (
    [Running total],
    CALCULATE ( SUM ( 'YourTableName'[Error Volume] ), ALL ( 'YourTableName' ) )
)

 

vkongfanfmsft_0-1712303592126.png

 

Best Regards,
Adamk Kong

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you! However, I am attempting to create a Pareto Chart with the data and therefore will be sorting the Errors based on Error Volume from largest to smallest and not alphabetically.

Below is what I am looking to accomplish.

Error                                     E.V.      R.T.     Chart %

Timeliness111134.38%
Appropriate Notes112268.75%
Appropriate Escalation42681.25%
Appropriate Activity Code32990.63%
Resolution332100.00%

 

Here is the resulting table I get when sorting the Errors by Error Volume:

ErrorsError VolumeRunning Total
Appropriate Notes1118
Timeliness1132
Appropriate Escalation47
Appropriate Activity Code33
Resolution321
Total32 

 

mol_ad
New Member

While testing the provided solution, I am getting the below display error.

mol_ad_0-1712171515089.png

 

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1712168228920.png

 

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

Running total: = 
CALCULATE (
    SUM ( 'Follow-Up Team'[Error Volume] ),
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALL ( 'Follow-Up Team' ),
        ORDERBY (
            CALCULATE ( SUM ( 'Follow-Up Team'[Error Volume] ) ), DESC,
            'Follow-Up Team'[Errors], ASC
        ),
        ,
        ,
        MATCHBY ( 'Follow-Up Team'[Errors] )
    )
)

 

ratio: = 
DIVIDE (
    [Running total:],
    CALCULATE ( SUM ( 'Follow-Up Team'[Error Volume] ), ALL ( 'Follow-Up Team' ) )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors