cancel
Showing results for
Did you mean:
Helper II

## How to get Cumulative total from measures to draw Pareto Chart

Dear all,

Recently I wanted to build a pareto chart with a table from direct query from sql server. I used direct query because

The data is as below:

The master table have rows with same issue, so the first issue is to group all data by issues and calculate the total losstime

with measures:

TotalLosstime = SUMX(VALUES(Losstime_Pareto[Issue]),CALCULATE(SUM(Losstime_Pareto[Losstime])))

Then I do a rankx measures:

Ranking1 = RANKX( ALLSELECTED(Losstime_Pareto[Issue]), 'Losstime_Pareto'[TotalLosstime])

Then I calculate total sales with measures:
Total Sales1 = CALCULATE(
SUM( Losstime_Pareto[Losstime] ) ,
ALLSELECTED( Losstime_Pareto)
)

Now the issue is I couldn't ge the cumulative total of the measures after I have group the issues. Anyone know how to solve this?
Existingly I use measures to calculate the cumulative total:
Cumulative1 =
CALCULATE (
Losstime_Pareto[TotalLosstime],
FILTER (
ALL(Losstime_Pareto[Issue]),
Losstime_Pareto[Ranking1] <= 5 )
)

1 ACCEPTED SOLUTION
Community Support

Hi, @jenas6423

If you want to interact with slicers, you may try replacing 'ALL' with 'ALLSELECTED' to see if it works.

``````TotalLosstime = SUM('Table'[Losstime])

Ranking1 =
RANKX(
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Sum],[TotalLosstime]
)

Cumulative1 =
SUMX(
FILTER(
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Issue]<=SELECTEDVALUE('Table'[Issue])
),
[Sum]
)

Cumulative total =
var tab =
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",SUM('Table'[Losstime])
)
var newtab =
tab,
"Rank",
COUNTROWS(
FILTER(
tab,
[Sum]<=EARLIER([Sum])
)
)+1
)
var t =
newtab,
"Val",
SUMX(
FILTER(
newtab,
[Rank]>=EARLIER([Rank])
),
[Sum]
)
)
return
MAXX(
SUMMARIZE(
'Table',
'Table'[Issue],
"Result",
var _issue = [Issue]
return
SUMX(
FILTER(
t,
[Issue]=_issue
),
[Val]
)
),
[Result]
)``````

Best Regards

Allan

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

7 REPLIES 7
Community Support

Hi, @jenas6423

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create measures as below.

``````TotalLosstime = SUM('Table'[Losstime])

Ranking1 =
RANKX(
SUMMARIZE(
ALL('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Sum],[TotalLosstime]
)

Cumulative1 =
SUMX(
FILTER(
SUMMARIZE(
ALL('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Issue]<=SELECTEDVALUE('Table'[Issue])
),
[Sum]
)

Cumulative total =
var tab =
SUMMARIZE(
ALL('Table'),
'Table'[Issue],
"Sum",SUM('Table'[Losstime])
)
var newtab =
tab,
"Rank",
COUNTROWS(
FILTER(
tab,
[Sum]<=EARLIER([Sum])
)
)+1
)
var t =
newtab,
"Val",
SUMX(
FILTER(
newtab,
[Rank]>=EARLIER([Rank])
),
[Sum]
)
)
return
MAXX(
SUMMARIZE(
'Table',
'Table'[Issue],
"Result",
var _issue = [Issue]
return
SUMX(
FILTER(
t,
[Issue]=_issue
),
[Val]
)
),
[Result]
)``````

Result:

Best Regards

Allan

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

Helper II

Hi Allan,

Very thank you for your reply. It does solve the issue for me. Really really appreciate ur help.

I will read through ur solution, as for now, it is quite complicated for me.

Meanwhile, do you know if I want to add in slicer for datetime and production line, how to change the dax code? Now seems like if I filter by slicer, by date and by line, the pareto will be off (over 100%)

I have uploaded my directquery data in excel form for your reference. Hope you can help. You are great. Thank you!

Regards.

Jenas

Data from direct query:

 ID Datetime Line Shift Issue Losstime 13 1/9/2020 R1 1 Minor Stop 111 14 1/9/2020 R1 1 Minor Stop 222 15 1/9/2020 R1 1 Minor Stop 333 2 6/1/2020 R1 1 Minor Stop 490 3 6/1/2020 R1 1 Machine Breakdown 349 4 6/1/2020 R1 1 Quality Defect 343 5 6/1/2020 R1 1 Parts Delay 161 6 6/1/2020 R1 1 Others 109 7 6/2/2020 F1 2 Minor Stop 490 8 6/2/2020 F1 2 Machine Breakdown 349 9 6/2/2020 F1 2 Quality Defect 343 10 6/2/2020 F1 2 Parts Delay 161 11 6/2/2020 F1 2 Others 109 16 3/9/2020 R1 1 Minor Stop 100 17 4/9/2020 R1 1 Minor Stop 100

Super User III

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi, @jenas6423

If you want to interact with slicers, you may try replacing 'ALL' with 'ALLSELECTED' to see if it works.

``````TotalLosstime = SUM('Table'[Losstime])

Ranking1 =
RANKX(
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Sum],[TotalLosstime]
)

Cumulative1 =
SUMX(
FILTER(
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",
CALCULATE(
SUM('Table'[Losstime]),
ALLEXCEPT('Table','Table'[Issue])
)
),
[Issue]<=SELECTEDVALUE('Table'[Issue])
),
[Sum]
)

Cumulative total =
var tab =
SUMMARIZE(
ALLSELECTED('Table'),
'Table'[Issue],
"Sum",SUM('Table'[Losstime])
)
var newtab =
tab,
"Rank",
COUNTROWS(
FILTER(
tab,
[Sum]<=EARLIER([Sum])
)
)+1
)
var t =
newtab,
"Val",
SUMX(
FILTER(
newtab,
[Rank]>=EARLIER([Rank])
),
[Sum]
)
)
return
MAXX(
SUMMARIZE(
'Table',
'Table'[Issue],
"Result",
var _issue = [Issue]
return
SUMX(
FILTER(
t,
[Issue]=_issue
),
[Val]
)
),
[Result]
)``````

Best Regards

Allan

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

Super User IV

@jenas6423 , Try like

Cumm Sales1 = CALCULATE(
SUM( Losstime_Pareto[Losstime] ) ,
filter(ALLSELECTED( Losstime_Pareto), Losstime_Pareto[Issue] <= max(Losstime_Pareto[Issue]))
)

Proud to be a Super User!

Helper II

Hi

Helper II

Hi  ,

After I use ur measures, the result will be like:

But the cumulative total should be based on the ranking1 column.

The correct output should be:

Regards.

Jenas

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!