Help a girl out with Paretho Chart!

[CARUSO93]

Hi,

I'm trying to construct a Paretho Chart that shows the 'Stock Item Codes' as well as the nr for each of them in the other axis. Then the line value is meant to show the cumulative percentage. 
I could download the 'Paretho Chart Visual' which resulted in the first picture, we can call it 'Picture A' (see below).

However, I need to be able to do this manually. This is where I need your help and advice.

As you can see in Picture A, the line is fine and shows the percentage in a cumulative 'manner'.

Picture A:

However, here then, in 'Picture B', when I have tried to construct it manually, it just won't work. The line is not correctly shaped and I get a sense of that the values aren't either.... The line should start from 0 and follow the shape of a curve not being stepped....

Picture B

and here is the table belonging to those measures;

Picture C shows the Table I did as well.

Table 'Categories' and 'MP711000' is where my data comes from. SIC stands for 'Stock Item Code'

So, I have used the following DAX Functions to construct the Pareto Chart as in 'Picture B'. I used 'Line and Clustered Column Chart'.

1. I first started with calculating the 'Total amount of SIC category' which resulted in this DAX-function;

                Total antal SIC Category =COUNT(MP711000﹛Stock Item Code﹜)

2. Then I created a table (See Picture C) where I first added 'Stock Item Code' from the 'MP711000' measures.

3. I then did a rank function which resulted in the following;

                 SIC RANK=IF(HASONEVALUE(MP71000﹛Stock Item Code﹜), RANKX(ALL(MP711000﹛Stock Item Code﹜), 'Stock Item                    Code Measures'﹛Total antal SIC category﹜), BLANK())

4. Then I calculated the 'Cumulative Total' which resulted in the following DAX functions;

                       SIC CUMUL TOTAL=IF(HASONEVALUE(MP711000﹛Stock Item Code﹜), SUMX(TOPN('Stock Item Code                                              Measures'﹛SIC RANK﹜, ALL(MP711000﹛Stock Item Code﹜), 'Stock Item Code Measures'﹛Total antal SIC                                        category﹜, DESC), Stock Item Code Measures'﹛Total antal SIC category﹜, BLANK ())

5. Then I calculated 'Total for all SIC Categories' which resulted in the following DAX-function;

                                 Total for all SIC Categories=SUMX(ALL(MP711000﹛Stock Item Code﹜),                                 
                                 'Stock Item Code Measures'﹛Total antal SIC category﹜)

6. Then finally, I calculated the 'Cumulative %' which resulted in the following;

                        SIC CUMUL PERCENTAGE=DIVIDE('Stock Item Code Measures'﹛SIC CUMUL TOTAL﹜, 
                       'Stock Item Code Measures'﹛Total for all SIC Categories﹜, 0)

Then lastly, after all the measures had been created, I choose the 'Line and clustered column chart' and to that chart I added 'Stock Item Code'(from the MP711000) and 'Total antal SIC category'. I then also added 'SIC CUMUL PERCENTAGE' and moved that last measure to 'Line Values'. I also did a calculation of 80% as that should be present in the Paretho Chart, and the calculation for that was simply;

                                SIC 80 PERCENT=0.8

Then I added that to the 'Line Values' as well.

So that's how I ended up with the chart as seen in Picture B. But my question remains:

What have I done wrong in the calculations?
Why isn't it working?

Why is the line not curved?
Why are the line values different from the 'auto constructed' Paretho Chart from the manually one that I created?

Please, help a girl out. I have watched numerous tutorials, google the various functions in an attempt to try and solve this and I really want to understand where I have gone wrong.

Hope to hear from you,

Thanks for a good community!

My best regards,

/E

[v-yetao1-msft]

Hi @CARUSO93 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ Ailsa Tao

[CARUSO93]

@lbendlin 

Many thanks, this is exactly what I am looking for. 

May I just ask;

How do I change the line value (the cumulative line) to display values from 0-100% and not 200%-2200%?

I clicked on the 'Cumulative Measure' and changed the formatting to 'Percentage' but then it gave me 200-2200%.

Lastly, have you worked many years with Power BI, or how do you get so good at it?

Again, many thanks!!!

[lbendlin]

Sorry, forgot to do the percentage thing. You can slightly tweak the measure

Cumul = 
var s = max(MP711000[SIC])
var c = countrows(MP711000)
var asl = calculatetable(values(MP711000[SIC]),REMOVEFILTERS(MP711000[SIC]))
var a = ADDCOLUMNS(asl,"si",[SIC],"ct",calculate(countrows(MP711000)))
var b = filter(a,[si]<=s && [ct]=c || [ct]>c)
return if(c>0,divide(sumx(b,[ct]),sumx(a,[ct]),0))

I'm still relatively new at Power BI, I try to learn as much as I can. I found that trying to answer questions in this forum, and presenting Power BI topics to a wider audience (in my case internal to my company) helps me improve my knowledge. 

[CARUSO93]

I enetered the updated measure, many thanks!!!

It's still not going up to a 100%. Only up to 35%.

Is it that it has to do with the filtering..? that you have to select all for the line to cumulate to 100%? 

[lbendlin]

Sounds like you have additional filters?  I only consider the category filter, based on the sample data you provided.  I would need to see the rest of your data model to be able to change the logic as needed.

[CARUSO93]

Here's one of my tables:  'Category'

Text	Category
1A	Komponent i r, ej i pl.
1B	Saknas på allt u, men måste mont.
2A	Fel antal mellan p och r.
2B	Fel antal i både p och r.
3A	Omkonst.
4A	Defekt.
5A	Konst fel

And here's my other table named 'MP711000'

Text	SIC
1A	84732113
1A	82800012-25
1B	84902126
1B	84986001
1B	051000002
1A	L81150128
1A	84744030
1A	82800012-25
1A	84984026
1A	81480026
1A	82800247
1B	21620117
1B	400270
1B	82830183
1B	84744032
1B	82280084
1B	HT00249-02
1B	81410432
1B	21620109
1B	HT00249-01
1B	041000235
1B	041000227
1B	L82416045
1B	041000235
1B	84744032
2A	84732120
2A	84103093
2A	21075820
2A	84369016
2A	400270
2B	84729028
2B	84701025
3A	82860161
3A	84949011-PL
3A	L84404641
3A	21070447
3A	84779219
3A	84779220
3A	21659695
3A	82860100
3A	84779228
3A	84779221
4A	84779208
4A	21658617
4A	21620224
4A	84787059
4A	84109020
4A	L82610033SF
4A	XX879
4A	21036307
5A	84732113
5A	21620225
5A	XX1181
5A	21076054
5A	MBP002-07
5A	84379005
5A	84908230
5A	MBP002-03
5A	MBP002-03

[lbendlin]

Within each of the value buckets how do you want to sort the SICs - alphabetically? if yes then here is an implementation proposal based on your data.

[CARUSO93]

Can I email you the file?

[lbendlin]

That's not an option, unfortunately.

[CARUSO93]

Where can I upload it? Which share service?

[lbendlin]

Whatver you prefer/feel comfortable with. OneDrive, DropBox, or similar.

[CARUSO93]

Would an Excel-file work?

[lbendlin]

yes, if you place it on an open file share service.  Not here.  Posts here only support flat tables.

[CARUSO93]

I downloaded a Paretho Chart visual, and dragged the sample data to it, which resulted in this;

That's how I expect my outcome to be, though I will just do it manually.

When I tried first as you can see in my first post above, the line (displaying the cumulative percentage) wasn't shaped like it is here. From that I make the assumption that there must be something in my calculations....

Hmmm........

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

[CARUSO93]

Ok, I see!

I will add an index column then to the 'Stock Item Code' column in the 'Power Query Editor'.

DId you customize the Indexs in your printscreen? If so, how did you do that? I tried, but mine shows 1-58, but I want it as in your printscreen, to display the same nr for every Stock Item Code that falls within the same category.

[lbendlin]

An index column serves to uniquely identify every row in your table and their sort order.  Don't try to find "workarounds" that would remove that property.

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[CARUSO93]

@lbendlin , Do you think the Index column will sort my problem?

Did you drag the measure you performed above to one of the visuals or?

[lbendlin]

Yes, the index column is required.

I used the combo column and line chart visual and put the measure in as the line value.

[lbendlin]

One thing that seems to be missing is an index column. Power BI doesn't know how you want to sort things, and therefore also doesn't know how to do the cumulative calculation. You use a RANKX but it's not clear what the criteria for that would be.

Let's assume we have this sample data

Then you can add your cumulative measure and display it.