Forum Discussion
Measure To Calculate Cumulative Percentage
Hi,
I am trying to create a graph that displays total hours per item, and then display a cumulative percentage as a line graph. Example from Excel attached.
Dataset: Cumulative % is not in the dataset. It is just a calculation I have done in Excel for example.
| Person | Hours | Cumulative % |
| A | 60 | 60% |
| B | 25 | 85% |
| C | 15 | 100% |
Dataset below is an example dataset similar to mine. I would like this graph to be able to drill up to the Team, while also drilling down to the reason. There are also other filters affecting this visual such as date.
| Team | Person | Reason | Hours |
| 1 | A | Cleaning | 20 |
| 1 | A | Cleaning | 10 |
| 1 | A | Serving | 30 |
| 2 | B | Serving | 20 |
| 2 | B | Cleaning | 5 |
| 1 | C | Cleaning | 15 |
Any assistance would be appreciated.
Hi Jarrod,
You can add an index field on query edit side based on person group, then you can write a more formula to calculate the cumulative percent based on current index.
rolling pect = VAR currIndex = MAX ( 'Table'[Index] ) VAR rolling = CALCULATE ( SUM ( 'Table'[Hours] ), FILTER ( ALLSELECTED ( 'Table' ), [Index] <= currIndex ) ) VAR total = CALCULATE ( SUM ( 'Table'[Hours] ), ALLSELECTED ( 'Table' ) ) RETURN DIVIDE ( rolling, total, -1 )Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYuec1MS8zLx0INPIQClWB6uMIapMcGpRGUTCGCJhBGQ6oUgYoUogmWUKN8oZzRKgTCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Person = _t, Reason = _t, Hours = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", Int64.Type}, {"Person", type text}, {"Reason", type text}, {"Hours", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Person"}, {{"Count", each _, type table [Team=nullable number, Person=nullable text, Reason=nullable text, Hours=nullable number]}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type), #"Expanded Count" = Table.ExpandTableColumn(#"Added Index", "Count", {"Team", "Reason", "Hours"}, {"Team", "Reason", "Hours"}) in #"Expanded Count"Regards,
Xiaoxin Sheng
7 Replies
Hi,
How have you calculated the % under the Cumulative % column? Also, show the expected result under various drill up and drill down scenarios.
It is just hardcoded in Excel as an example. I am trying to build a Pareto chart.
Hi Jarrod,
You can add an index field on query edit side based on person group, then you can write a more formula to calculate the cumulative percent based on current index.
rolling pect = VAR currIndex = MAX ( 'Table'[Index] ) VAR rolling = CALCULATE ( SUM ( 'Table'[Hours] ), FILTER ( ALLSELECTED ( 'Table' ), [Index] <= currIndex ) ) VAR total = CALCULATE ( SUM ( 'Table'[Hours] ), ALLSELECTED ( 'Table' ) ) RETURN DIVIDE ( rolling, total, -1 )Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYuec1MS8zLx0INPIQClWB6uMIapMcGpRGUTCGCJhBGQ6oUgYoUogmWUKN8oZzRKgTCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Person = _t, Reason = _t, Hours = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", Int64.Type}, {"Person", type text}, {"Reason", type text}, {"Hours", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Person"}, {{"Count", each _, type table [Team=nullable number, Person=nullable text, Reason=nullable text, Hours=nullable number]}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type), #"Expanded Count" = Table.ExpandTableColumn(#"Added Index", "Count", {"Team", "Reason", "Hours"}, {"Team", "Reason", "Hours"}) in #"Expanded Count"Regards,
Xiaoxin Sheng
