Pareto Chart with DAX Function

[JajatiDev]

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

[LucasPete]

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

[v-jayw-msft · Accepted solution]

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

[Whitewater100]

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

[Whitewater100]

Hello:

Did my reply answer your initial question? Thanks..

[Whitewater100]

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!

[Whitewater100]

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!

[Anonymous]

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

[JajatiDev]

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