cancel
Showing results 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.

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
Post Patron

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

New Member

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 %

 Timeliness 11 11 34.38% Appropriate Notes 11 22 68.75% Appropriate Escalation 4 26 81.25% Appropriate Activity Code 3 29 90.63% Resolution 3 32 100.00%

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

 Errors Error Volume Running Total Appropriate Notes 11 18 Timeliness 11 32 Appropriate Escalation 4 7 Appropriate Activity Code 3 3 Resolution 3 21 Total 32

Community Support

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

Best Regards,

New Member

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 Identifier User Supervisor Month Errors Error Volume Efficiency Score Quarter T.B.-January T.B. Supervisor #1 January Timeliness 2 60% Q1 T.B.-January T.B. Supervisor #1 January Appropriate Activity Code 0 100% Q1 T.B.-January T.B. Supervisor #1 January Appropriate Notes 1 80% Q1 T.B.-January T.B. Supervisor #1 January Appropriate Escalation 1 80% Q1 T.B.-January T.B. Supervisor #1 January Resolution 0 100% Q1 T.B.-February T.B. Supervisor #1 February Timeliness 2 60% Q1 T.B.-February T.B. Supervisor #1 February Appropriate Activity Code 2 60% Q1 T.B.-February T.B. Supervisor #1 February Appropriate Notes 3 40% Q1 T.B.-February T.B. Supervisor #1 February Appropriate Escalation 0 100% Q1 T.B.-February T.B. Supervisor #1 February Resolution 1 80% Q1 T.B.-March T.B. Supervisor #1 March Timeliness 3 40% Q1 T.B.-March T.B. Supervisor #1 March Appropriate Activity Code 0 100% Q1 T.B.-March T.B. Supervisor #1 March Appropriate Notes 2 60% Q1 T.B.-March T.B. Supervisor #1 March Appropriate Escalation 0 100% Q1 T.B.-March T.B. Supervisor #1 March Resolution 1 80% Q1 L.B.-January L.B. Supervisor #2 January Timeliness 1 80% Q1 L.B.-January L.B. Supervisor #2 January Appropriate Activity Code 0 100% Q1 L.B.-January L.B. Supervisor #2 January Appropriate Notes 2 60% Q1 L.B.-January L.B. Supervisor #2 January Appropriate Escalation 1 80% Q1 L.B.-January L.B. Supervisor #2 January Resolution 0 100% Q1 L.B.-February L.B. Supervisor #2 February Timeliness 3 40% Q1 L.B.-February L.B. Supervisor #2 February Appropriate Activity Code 0 100% Q1 L.B.-February L.B. Supervisor #2 February Appropriate Notes 0 100% Q1 L.B.-February L.B. Supervisor #2 February Appropriate Escalation 0 100% Q1 L.B.-February L.B. Supervisor #2 February Resolution 1 80% Q1 L.B.-March L.B. Supervisor #2 March Timeliness 0 100% Q1 L.B.-March L.B. Supervisor #2 March Appropriate Activity Code 1 80% Q1 L.B.-March L.B. Supervisor #2 March Appropriate Notes 3 40% Q1 L.B.-March L.B. Supervisor #2 March Appropriate Escalation 2 60% Q1 L.B.-March L.B. Supervisor #2 March Resolution 0 100% Q1

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

Error                                     E.V.      .T.     Chart %

 Timeliness 11 11 34.38% Appropriate Notes 11 22 68.75% Appropriate Escalation 4 26 81.25% Appropriate Activity Code 3 29 90.63% Resolution 3 32 100.00%
Community Support

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

Best Regards,

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

New Member

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 %

 Timeliness 11 11 34.38% Appropriate Notes 11 22 68.75% Appropriate Escalation 4 26 81.25% Appropriate Activity Code 3 29 90.63% Resolution 3 32 100.00%

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

 Errors Error Volume Running Total Appropriate Notes 11 18 Timeliness 11 32 Appropriate Escalation 4 7 Appropriate Activity Code 3 3 Resolution 3 21 Total 32

New Member

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

Super User

Hi,

Please check the below picture and the attached pbix file.

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.

Announcements

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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors