cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
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:

jenas6423_0-1599210855212.png

 

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

image.png

 

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

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.

View solution in original post

7 REPLIES 7
Community Support
Community Support

Hi, @jenas6423 

 

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

Table:

g1.png

 

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:

g2.png

 

Best Regards

Allan

 

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

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

 

jenas6423_0-1599466082586.png

 

jenas6423_1-1599466110611.png

 

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:

IDDatetimeLineShiftIssueLosstime
131/9/2020R11Minor Stop111
141/9/2020R11Minor Stop222
151/9/2020R11Minor Stop333
26/1/2020R11Minor Stop490
36/1/2020R11Machine Breakdown349
46/1/2020R11Quality Defect343
56/1/2020R11Parts Delay161
66/1/2020R11Others109
76/2/2020F12Minor Stop490
86/2/2020F12Machine Breakdown349
96/2/2020F12Quality Defect343
106/2/2020F12Parts Delay161
116/2/2020F12Others109
163/9/2020R11Minor Stop100
174/9/2020R11Minor Stop100

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

View solution in original post

Super User IV
Super User IV

@jenas6423 , Try like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi  

 

 

Hi  ,

 

Thank you for ur reply.

 

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

image.png

 

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

The correct output should be:
image.png

 

Regards.

 

Jenas

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors