Forum Discussion

JajatiDev's avatar
JajatiDev
Helper II
3 years ago
Solved

Creating A Pareto Chart using DAX Function Combinations

Hello All,

Greeting!

 

While exploring DAX functions, I came across this beautiful demonstration to Create A Pareto Chart In Power BI Using DAX Function Combinations from Enterprise DNA. However, I have failed to replicate the output with my data set. Therefore, looking for assistance.

 

The measure used for calculating;

1. NoOfOrderLines = 

VAR ShipAsAvailable = FILTER('Table (ClosedOrders)', 'Table (ClosedOrders)'[delivery_type] = "Ship as available")
VAR Complete_Table = FILTER(SUMMARIZE('Table (ClosedOrders)','Table (ClosedOrders)'[order_no],
            'Table (ClosedOrders)'[delivery_type]), 'Table (ClosedOrders)'[delivery_type] = "Full order consolidation")
Return
    COUNTROWS(ShipAsAvailable) + COUNTROWS(Complete_Table)
 
2. TotalNoOfLines =
CALCULATE([NoOfOrderLines], ALLSELECTED('Table(ClosedOrders)'))
 
I need assistance with the measure to calculate the Cumulative total and the Percentage to complete the table and create the graph.
 
I am looking forward to hearing from you.
 
DaysNoOfOrderLinesCumulative TotalTotalNoOfLinesPercentage
06643 12220 
1597 12220 
2201 12220 
3226 12220 
4291 12220 
5397 12220 
6300 12220 
7306 12220 
8340 12220 
9268 12220 
10236 12220 
11179 12220 
12206 12220 
13171 12220 
14199 12220 
15213 12220 
16141 12220 
17118 12220 
18170 12220 
19143 12220 
20118 12220 
21136 12220 
22114 12220 
23109 12220 
24130 12220 
2588 12220 
2696 12220 
2784 12220 
  • sevenhills's avatar
    sevenhills
    3 years ago

    Try adding these two measures:

     

    NoOfOrderLines running total in Days = 
CALCULATE(
	SUM('Table'[NoOfOrderLines]),
	FILTER(
		ALLSELECTED('Table'[Days]),
		ISONORAFTER('Table'[Days], MAX('Table'[Days]), DESC)
	)
)

     

    % Calc = DIVIDE([NoOfOrderLines running total in Days], Max('Table'[TotalNoOfLines]))

    and format the % measure as

     

    Table visual:

    Pareto chart visual using Line and stacked column chart

    output:

     

     

     Hope it helps!

  • sevenhills's avatar
    sevenhills
    3 years ago

    I looked into your file and tried to understand some and could not get what is target of 90%. 

     

    Providing the details of measures, tune the names and syntax for your needs.

     

     

    1. Counts Full Consolidation at Origin = 
VAR FullOrderConsolidation = FILTER(
                                 SUMMARIZE('DataTable','DataTable'[Order_ID], 'DataTable'[Delivery_Type]), 
                                 'DataTable'[Delivery_Type] = "Full consolidation at origin")

Return COUNTROWS(FullOrderConsolidation)

     

     

     

    1. Counts Ship as Available = 

VAR ShipAsAvailable = FILTER('DataTable', 'DataTable'[Delivery_Type] = "Ship as available")

Return COUNTROWS(ShipAsAvailable)

     

     

     

    2. No of order lines = [1. Counts Ship as Available] + [1. Counts Full Consolidation at Origin]

     

     

     

    3. Cumulative Total No of order lines = 
CALCULATE( [2. No of order lines], FILTER( ALLSELECTED('DataTable'), 'DataTable'[SC_TAT] <= MAx('DataTable'[SC_TAT])))

     

     

     

    3. Running % CT and Total = 
DIVIDE( [3. Cumulative Total No of order lines], CALCULATE( [2. No of order lines], ALLSELECTED('DataTable') ), BLANK())

     

     

    Sample output:

     

     

    Note: You have to check your requirements as SC_TAT going up to 400 values on x-axis, which is not good in my view. 

     

    Hope this helps!

10 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    3 years ago

    Try adding these two measures:

     

    NoOfOrderLines running total in Days = 
CALCULATE(
	SUM('Table'[NoOfOrderLines]),
	FILTER(
		ALLSELECTED('Table'[Days]),
		ISONORAFTER('Table'[Days], MAX('Table'[Days]), DESC)
	)
)

     

    % Calc = DIVIDE([NoOfOrderLines running total in Days], Max('Table'[TotalNoOfLines]))

    and format the % measure as

     

    Table visual:

    Pareto chart visual using Line and stacked column chart

    output:

     

     

     Hope it helps!

    • JajatiDev's avatar
      JajatiDev
      Helper II
      3 years ago

      sevenhills thank you. I appreciate the effort. However, I made a mistake while explaining my requirement due to which the proposed solution isn't giving the desired result. Therefore, I'm providing the raw data to deliver the solution as intended.

       

      The Delivery_Type field is extremely critical while calculating NoOfOrderLines, because

      1. If, Delivery_Type is "Ship as available" then each so line within the order is treated independently. This implies if an order has 4 lines, then each line is treated separately and will be counted as 4 Order Lines
      2. If, Delivery_Type is "Full consolidation at origin" then no matter how mail lines are there in the order it will always be counted as 1 Order Line

      Due to the above criteria, I create the measure NoOfOrderLines. However, I'm unable to convert this measure into a variable because I'm unsure of the combination of DAX Functions needed to do so.

       

      I require a single measure that would deliver me the Pareto Chart as demonstrated in the video link shared.

       

      Here is the link to the raw data as I'm unable to share in this string.

       

      • sevenhills's avatar
        sevenhills
        Super User
        3 years ago

        Unauthorized

        Error 401

        the file link is unauthorized. 

         

        Post some data - copy paste

        and also what is the output you are expecting, that way anyone can help.

         

        Looks like you are trying to consolidate orders with availability of sources either use as 1 or multiple.  It should be simple calc. 

         

    • JajatiDev's avatar
      JajatiDev
      Helper II
      3 years ago

      I need to create a Pareto Chart.

      Shared below is the measure to calculate NoOfOrderLines.

      Using the NoOfOrderLines measure I need assistance with the measure to calculate the cumulative total, percentage and 90% threshold. Table data is shared below as well.

       

      This is the measure for calculating NoOfOrderLines;

      NoOfOrderLines = 

      VAR ShipAsAvailable = FILTER('Table (ClosedOrders)', 'Table (ClosedOrders)'[delivery_type] = "Ship as available")
      VAR Complete_Table = FILTER(SUMMARIZE('Table (ClosedOrders)','Table (ClosedOrders)'[order_no],
                  'Table (ClosedOrders)'[delivery_type]), 'Table (ClosedOrders)'[delivery_type] = "Full order consolidation")
      Return
          COUNTROWS(ShipAsAvailable) + COUNTROWS(Complete_Table)
       
      I need assistance with the cumulative total and percentage. I also need to create an intersection of 90%

       

      SegmentCustomer_IDOrder_IDItem_NoProduct_IDQuantityPlantDelivery_TypeProduct_GroupSC_TATItem_PriceOrder_Value

      Enterprise16113186631111661181104D937BV1TX39Full consolidation at originCFH11173805.5530458.08
      Enterprise161131866311116611812004D937BV1TX39Full consolidation at originCFH11173805.5530458.08
      Enterprise161131866311116611813904D937BV1TX39Full consolidation at originCFH11173805.5530458.08
      Enterprise161131866311116611815804D937BV1TX39Full consolidation at originCFH11173805.5530458.08
      Enterprise161131866311116611817704D937BV1TX39Full consolidation at originCFH11173805.5530458.08
      Enterprise161131866311116611819604D937BV1TX39Full consolidation at originCFH11173805.5530458.08
      Enterprise1611318663111166118111504D937BV1TX39Full consolidation at originCFH11173805.5530458.08
      Enterprise1611318663111166118113404D937BV1TX39Full consolidation at originCFH11173805.5530458.08
      Corporate15118286151113431261102F2P2BV1TX39Full consolidation at originCFH11333659.74659.74
      Corporate15118286151113651116102F2P2BV1TX39Full consolidation at originCFH11333659.74659.74
      Corporate15118286151113713254102F2P2BV1TX39Full consolidation at originCFH11328659.74659.74
      Corporate15118286151113757653102D3R6BV1TX39Full consolidation at originCFH11325757.89757.89
      Corporate15118286151113757655102D3R6BV1TX39Full consolidation at originCFH11325757.89757.89
      Corporate16112856161113666321103ST25B-DCH1TX39Ship as availableCFH113145547.65385.36
      Corporate16112856161114126271103ST25B-DCH1TX39Ship as availableCFH113015547.65578.56
      Corporate16112856161114168784103ST25B-DCH1TX39Ship as availableCFH112885547.65548.56
      Corporate16112856161114166163103ST25B-DCH1TX39Ship as availableCFH113095547.65385.36
      Corporate16112856161114236167103ST25B-DCH2TX39Ship as availableCFH113008895.83799.58
      Corporate16113186631114276816103ST25B-DCH1TX39Ship as availableCFH112895567.855409.85
      Corporate1611318663111427681620B5K53B1TX39Ship as availableC4A11217554.655409.85
      Corporate1611318663111427681630V1B53B-DCH1TX39Ship as availableC8V11217587.355409.85
      Corporate16113186631114281418103ST25B-DCH1TX39Full consolidation at originCFH112895567.855888.5
      Corporate1611318663111428141820B5K53B1TX39Full consolidation at originC4A11289554.655888.5
      Corporate16113186631114412871103ST25B-DCH1TX39Ship as availableCFH112835567.855409.85
      Corporate1611318663111441287120B5K53B1TX39Ship as availableC4A11211554.655409.85
      Corporate1611318663111441287130V1B53B-DCH1TX39Ship as availableC8V11211587.355409.85
      Corporate16113186631114412866103ST25B-DCH1TX39Full consolidation at originCFH112835567.855888.5
      Corporate1611318663111441286620B5K53B1TX39Full consolidation at originC4A11283554.655888.5
      Corporate16112856161114446662103ST25B-DCH1TX39Ship as availableCFH112815547.65578.56
      Corporate16112856161114462157103ST25B-DCH1TX39Ship as availableCFH112895547.65653.04
      Enterprise1611318663111456813510J7Z14B-B2M1TX39Full consolidation at originC9D112203004.353544.04
      Enterprise16113186631114568135209UV22B1TX39Full consolidation at originCGR11220499.883544.04
      Enterprise16113186631114568135302GH31B1TX39Full consolidation at originC4A1122040.453544.04
      • sevenhills's avatar
        sevenhills
        Super User
        3 years ago

        Post the data, DAX and other related info matching ... I am getting few errors and tried to fix as I progress.  Let us start using this, a little cleaner version of yours.

         

        Table with data: 

         

         

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZZbj9owEIX/CuJ514pnPPb4sYGyqFWlaneLqq72IW1RGwkRBLS/v+Ok3HZp4zi9gESwQfh8Pj4e++Fh+HK5na9X63IzH14NtdUaNVuLoSMvK1+wDp1MHmbs0eWz0JX3/Xv08jH5tlgMPlXLTbUoPxfbsloOiu2gWpdfyqX8PJpMdT2AkwdyRoootDJDrDIePl5FM0B2ARDoLwCC+AIgnLsACG8vAEJrugQKNH+TYlStV9W62NYQJJrAVlMDgQY12H2dgAm8hUQExDAxS145c2i0AlgKVvxHAKcRyPwRAOAkAHKWcA8wxlubDBDGlPEU+0MjDoD+IYAEH5hss+whA7IpYB9CvLsHyq/Ho+kpw93XcjUoNoPie1Euio+L+bGwDo4TGafCoIRMCm2rspHwg+unnOkTZXKsKEbZsmPTRxmYT5VNrLK0sd+cfZLbgNJ2Z5UhVjn8k9mTCpNH572iM2XuSa014Cwfqkya282crUQ6hJxM5kMrXhqCdE6vCfMIWfOika0ru1itbJoqBtWZzkU1csI8O1Zmp7CbMkvEuM3qyILyzHVmCXkHiF+a3gaw8/8nwM7/SH0pLNyvrgBjWt720ol500l526v2yJtOyltQtq1bOzpvT1zvsN72t5s8Om8YmbeTymqMnKHQL29p55ixoOl8Te9YWXfKchlSmWm/QBuSEof7e8sr90Gb/DqHN92M9+OGol45OV9Mkz8kYzpi1CP4dzOAjmt/c3uEYORIq0/2BIJ688HNFHVi+hqCTJkTCx5/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Segment = _t, Customer_ID = _t, Order_ID = _t, Item_No = _t, Product_ID = _t, Quantity = _t, Plant = _t, Delivery_Type = _t, Product_Group = _t, SC_TAT = _t, Item_Price = _t, Order_Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Segment", type text}, {"Customer_ID", Int64.Type}, {"Order_ID", Int64.Type}, {"Item_No", Int64.Type}, {"Product_ID", type text}, {"Quantity", Int64.Type}, {"Plant", type text}, {"Delivery_Type", type text}, {"Product_Group", type text}, {"SC_TAT", Int64.Type}, {"Item_Price", type number}, {"Order_Value", type number}})
in
    #"Changed Type"

         

         

         

         

        Measure "NoOfOrderLines"

         

         

         

         

        NoOfOrderLines = 

VAR ShipAsAvailable = FILTER('Table', 'Table'[Delivery_Type] = "Ship as available")
VAR Complete_Table = FILTER(SUMMARIZE('Table','Table'[Order_ID],
            'Table'[Delivery_Type]), 'Table'[Delivery_Type] = "Full consolidation at origin") -- "Full order consolidation"
Return
    COUNTROWS(ShipAsAvailable) + COUNTROWS(Complete_Table)

         

         

         

         

        I am getting error when I tried the measure (which I replied and posted based on your original code)...

         

         

         

         

        NoOfOrderLines running total in Days = 
CALCULATE(
	'Table'[NoOfOrderLines],
	FILTER(
		ALLSELECTED('Table'[Days]),
		ISONORAFTER('Table'[Days], MAX('Table'[Days]), DESC)
	)
)

         

         

         

        Model view

        Can you post what is days here ? I dont see the column here... 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  JajatiDev ,

     

    Is your problem solved, if it is solved, you can mark the correct answer, if not, you can explain your problem in detail, we can help you better.

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.