topic Re: Help a girl out with Paretho Chart! in DAX Commands and Tips

Help a girl out with Paretho Chart!

Anonymous — Fri, 18 Mar 2022 14:23:31 GMT

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'.

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,

Re: Help a girl out with Paretho Chart!

lbendlin — Sat, 19 Mar 2022 02:45:23 GMT

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.

Re: Help a girl out with Paretho Chart!

Anonymous — Mon, 21 Mar 2022 09:16:41 GMT

Hi lbendlin ,

Thanks for your reply.
I have Index Column, if it's the Headers you mean?

I did the cumulative measure and it worked fine, so far so good. However, when tried to add the measure to the 'Line and Clustered Column Chart' it won't work and appears as the second picture below;

Why is that?

When you did the cumulative measure how did you get that into the chart?

Again, big thanks for the reply.

Re: Help a girl out with Paretho Chart!

lbendlin — Mon, 21 Mar 2022 15:12:42 GMT

Click on the "See details" link and evaluate the error message.

An index column serves to uniquely identify every row in your table and their sort order. I don't see any column in your screenshot that looks like it could be used as an index.

Re: Help a girl out with Paretho Chart!

Anonymous — Tue, 22 Mar 2022 07:30:46 GMT

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?

Re: Help a girl out with Paretho Chart!

lbendlin — Tue, 22 Mar 2022 11:20:15 GMT

Yes, the index column is required.

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

Re: Help a girl out with Paretho Chart!

Anonymous — Tue, 22 Mar 2022 12:43:04 GMT

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.

Re: Help a girl out with Paretho Chart!

lbendlin — Tue, 22 Mar 2022 13:14:45 GMT

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.

Re: Help a girl out with Paretho Chart!

Anonymous — Tue, 22 Mar 2022 13:21:54 GMT

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........

Re: Help a girl out with Paretho Chart!

lbendlin — Tue, 22 Mar 2022 13:30:33 GMT

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.

Re: Help a girl out with Paretho Chart!

Anonymous — Tue, 22 Mar 2022 13:52:05 GMT

Would an Excel-file work?

Re: Help a girl out with Paretho Chart!

lbendlin — Tue, 22 Mar 2022 13:58:12 GMT

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

Re: Help a girl out with Paretho Chart!

Anonymous — Tue, 22 Mar 2022 14:07:08 GMT

Where can I upload it? Which share service?

Re: Help a girl out with Paretho Chart!

lbendlin — Tue, 22 Mar 2022 14:15:21 GMT

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

Re: Help a girl out with Paretho Chart!

Anonymous — Tue, 22 Mar 2022 14:24:44 GMT

Can I email you the file?

Re: Help a girl out with Paretho Chart!

lbendlin — Tue, 22 Mar 2022 14:27:29 GMT

That's not an option, unfortunately.

Re: Help a girl out with Paretho Chart!

Anonymous — Tue, 22 Mar 2022 15:14:43 GMT

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

Re: Help a girl out with Paretho Chart!

lbendlin — Tue, 22 Mar 2022 23:08:31 GMT

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.

Re: Help a girl out with Paretho Chart!

Anonymous — Wed, 23 Mar 2022 08:03:31 GMT

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!!!

Re: Help a girl out with Paretho Chart!

lbendlin — Wed, 23 Mar 2022 12:33:56 GMT

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.