topic Re: Pareto Chart with DAX Function in DAX Commands and Tips

Pareto Chart with DAX Function

JajatiDev — Tue, 17 May 2022 10:12:37 GMT

Hi,

This is a follow-up to my previous query.

I am trying to create a DAX function to convert the below table into a Pareto Chart.

x-axis: SC_TAT days

y-axis: CountOfOrderLines

Please assist with the DAX function that would capture the cumulative, total and percentage in one measure which can then be converted into a chart.

Regards,

Jajati Dev

SC_TAT (Days)	CountOfOrderLines	Cumulative	Total	Percentage
0	6643
1	597
2	201
3	226
4	291
5	397
6	300
7	306
8	340
9	268
10	236
11	179
12	206
13	171
14	199
15	213
16	141
17	118
18	170
19	143
20	118
21	136
22	114
23	109
24	130
25	88
26	96

Re: Pareto Chart with DAX Function

Anonymous — Tue, 17 May 2022 10:41:59 GMT

Hi jajatidev,

Please find attached required help,

I took your datas and saved table as Pareto.

After 2 measures :

cumulative =
VAR compteur =
    SELECTEDVALUE ( Pareto[SC_TAT (Days)] )
VAR result =
    CALCULATE (
        SUM ( Pareto[CountOfOrderLines] ),
        ALL ( Pareto ),
        Pareto[SC_TAT (Days)] <= compteur
    )
RETURN
    result

and the percentage :

cumulative percent =
VAR compteur =
    SELECTEDVALUE ( Pareto[SC_TAT (Days)] )
VAR totalorederline =
    CALCULATE ( SUM ( Pareto[CountOfOrderLines] ), ALL ( pareto ) )
VAR cumulative =
    CALCULATE (
        SUM ( Pareto[CountOfOrderLines] ),
        ALL ( Pareto ),
        Pareto[SC_TAT (Days)] <= compteur
    )
VAR result =
    DIVIDE ( cumulative, totalorederline )
RETURN
    result

Re: Pareto Chart with DAX Function

Whitewater100 — Tue, 17 May 2022 10:46:13 GMT

Hello:

You can try a couple calculated columns, after one initial measure

Measure ... Order Lines = SUM(Table[CountofOrderLines])

Cumulated Orders =

CALCULATE(

[Order Lines] ,

ALL( Table ),

Table[Order Lines] >= EARLIER( Table[Order Lines ))

Cumulated Percentage = DIVIDE( Table[Cumulated Orders] , SUM( Table[CountofOrderLines] ) , 0 )

I hope this helps!

Re: Pareto Chart with DAX Function

JajatiDev — Tue, 17 May 2022 17:36:33 GMT

Hi James,

Thanks for the quick response.

Could you please share the .pbix file for me to review because while trying to replicate I am not getting the desired result?

Regards,

Jajati Dev

Re: Pareto Chart with DAX Function

Whitewater100 — Tue, 17 May 2022 18:50:03 GMT

Hi:

I updated the calcs. Please see attached file. I hope this solves your question! Thanks.https://drive.google.com/file/d/1AmZdKZ0R0YYwGR4bpT36QFM8U_yePF7k/view?usp=sharing

I hope this helps!

Re: Pareto Chart with DAX Function

Whitewater100 — Tue, 17 May 2022 19:00:31 GMT

Hi:

You my be looking for this version. It's on the file upload. It's more in order of day number.

Cumulated Orders V2 =

VAR currorders = 'Table'[SC_TAT (Days)]

return

SUMX(

FILTER('Table',

'Table'[SC_TAT (Days)] >= currorders),

'Table'[CountOfOrderLines])

Re: Pareto Chart with DAX Function

Anonymous — Fri, 20 May 2022 07:11:16 GMT

Hi JajatiDev ,

Please check the formulas.

Cumulative = SUMX(FILTER(ALLSELECTED('Table'),'Table'[SC_TAT (Days)]<=MAX('Table'[SC_TAT (Days)])),'Table'[CountOfOrderLines])

Total = SUMX(ALLSELECTED('Table'),'Table'[CountOfOrderLines])

Percentage = format(SUMX('Table','Table'[CountOfOrderLines])/[Total],"percent")

Pbix as attached.

Best Regards,

Jay

Re: Pareto Chart with DAX Function

Whitewater100 — Fri, 20 May 2022 12:50:54 GMT

Hello:

Did my reply answer your initial question? Thanks..

Re: Pareto Chart with DAX Function

LucasPete — Fri, 20 Oct 2023 11:31:55 GMT

Hi,

How to build a pareto chart for one column?
I have a column - "Failures", in which errors occur repeatedly. On the column chart, unique names of the failure should appear on the X axis, and on the Y axis, the number of times it repeated should appear. The pareto chart will be a line of the % occurrence of a given failure from the entire table.

Thanks for the help in advance