Forum Discussion
Creating A Pareto Chart using DAX Function Combinations
- 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!
- 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!
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 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
- 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
- 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.
- sevenhills3 years agoSuper User
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.