Forum Discussion

JajatiDev's avatar
JajatiDev
Icon for Helper II rankHelper II
4 years ago
Solved

Pareto Chart with DAX Function

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)CountOfOrderLinesCumulativeTotalPercentage
06643   
1597   
2201   
3226   
4291   
5397   
6300   
7306   
8340   
9268   
10236   
11179   
12206   
13171   
14199   
15213   
16141   
17118   
18170   
19143   
20118   
21136   
22114   
23109   
24130   
2588   
2696   
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      JajatiDev
      Icon for Helper II rankHelper II
      4 years ago

      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

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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!
  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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's avatar
      Whitewater100
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hello:

       

      Did my reply answer your initial question? Thanks..

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

  • LucasPete's avatar
    LucasPete
    Regular Visitor
    2 years ago

    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