How to get Cumulative total from measures to draw Pareto Chart

[jenas6423]

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 )

)

[v-alq-msft]

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 = 
ADDCOLUMNS(
    tab,
    "Rank",
    COUNTROWS(
        FILTER(
            tab,
            [Sum]<=EARLIER([Sum])
        )
    )+1
)
var t = 
ADDCOLUMNS(
    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.

[jenas6423]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-alq-msft · Accepted solution]

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 = 
ADDCOLUMNS(
    tab,
    "Rank",
    COUNTROWS(
        FILTER(
            tab,
            [Sum]<=EARLIER([Sum])
        )
    )+1
)
var t = 
ADDCOLUMNS(
    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.

[amitchandak]

@jenas6423 , Try like

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

[jenas6423]

Hi  amitchandak,

Any feedback for this? Appreciate ur help. Thank you.

Regards.

Jenas

[jenas6423]

Hi amitchandak ,

Thank you for ur reply.

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