by group
1 Topic
Running Total by Count of Action - Pareto Chart
I am wanting to create a Pareto Chart based on the number of times an item is shipped. My data is an ItemLedger that lists a ship under TransactionType as "ORD.SHIP". For Example, 500001853 was shipped 4 times My Data: BranchID ItemID UserID TransactionType LotNumber TransactionNumber Quantity BinID LedgerDateTime Index LedgerDate DC01 500001846 CPEREZ ORD.SHIP 30000534-02-1 C100280514 -24 <NONE> 10/19/2020 365321 10/19/2020 DC01 500001853 CPEREZ ORD.SHIP >KGK1BAAA C100253075 -802 <NONE> 8/25/2020 286477 8/25/2020 DC01 500001853 CPEREZ ORD.SHIP >60KFBDAA C100255963 -19198 <NONE> 8/27/2020 289691 8/27/2020 DC01 500001853 CPEREZ ORD.SHIP >20BGBDAA D1033372 -39600 <NONE> 11/23/2020 430394 11/23/2020 DC01 500001853 JOSER PHYSINV.POST >60KFBDAA 00029711 -3 <NONE> 11/22/2020 429531 11/22/2020 DC01 500001853 QADGTW2 INV.DISCREP -3 11/26/2020 435286 11/26/2020 DC01 500001853 QADGTW2 INV.DISCREP -3 11/28/2020 435304 11/28/2020 DC01 500001853 QADGTW2 INV.DISCREP -3 11/30/2020 435326 11/30/2020 DC01 500001853 CPEREZ ORD.SHIP >60KFBDAA D1033372 -400 <NONE> 11/23/2020 430395 11/23/2020 DA01 500001854 CPEREZ ORD.SHIP >S5J9BDAA D1024557 -5 <NONE> 6/5/2020 194110 6/5/2020 DC01 500001854 JOSER ORD.SHIP >8GP1BAAA 00029711 36 <NONE> 11/22/2020 429532 11/22/2020 DC01 500001854 QADGTW2 ORD.SHIP 36 11/26/2020 435287 11/26/2020 DC01 500001854 QADGTW2 INV.DISCREP 36 11/28/2020 435305 11/28/2020 DC01 500001854 QADGTW2 INV.DISCREP 36 11/30/2020 435328 11/30/2020 DC01 500001881 CPEREZ ORD.SHIP >01MFBDAA C100258529 -8000 <NONE> 9/9/2020 301665 9/9/2020 DA01 500001881 CPEREZ ORD.SHIP >TDF0BAAA 00031146 -3 <NONE> 1/13/2021 494244 1/13/2021 DC01 500001884 CPEREZ ORD.SHIP >NR8QAAAA D1031203 -25 <NONE> 10/14/2020 358872 10/14/2020 DC01 500001884 JOSER PHYSINV.POST >1D6ZAAAA 00030055 -2 <NONE> 12/3/2020 444332 12/3/2020 Expected Result: ItemID Count of ORD.SHIP Running Total HELPER - TOTAL SHIPPED % of Total 500001853 4 4 11 36.4% 500001854 3 7 11 63.6% 500001881 2 9 11 81.8% 500001846 1 10 11 90.9% 500001884 1 11 11 100.0% 500001883 0 11 11 100.0% Notes: I was able to make a few measures but am having a hard time putting it all together. I am fine with starting over from scratch and am more focused on improving. Lines Shipped = CALCULATE(COUNT(ItemLedger[TransactionType]), ItemLedger[TransactionType] = "ORD.SHIP") Rank = RANKX(ALLSELECTED(ItemLedger[ItemID]),CALCULATE(COUNT(ItemLedger[TransactionType]),ItemLedger[TransactionType] = "ORD.SHIP")) Cumalative Shippments (Doesnt work as intended)= VAR IndexRank = [Rank] return SUMX( FILTER( SUMMARIZE(ItemLedger, ItemLedger[ItemID], "Total Shipped", CALCULATE(COUNT(ItemLedger[TransactionType]), ItemLedger[TransactionType] = "ORD.SHIP"), "Ship Rank", RANKX(ALLSELECTED(ItemLedger[ItemID]),CALCULATE(COUNT(ItemLedger[TransactionType]),ItemLedger[TransactionType] = "ORD.SHIP"))), [Ship Rank] <= IndexRank), CALCULATE(COUNT(ItemLedger[TransactionType]), ItemLedger[TransactionType] = "ORD.SHIP") )Solved881Views0likes1Comment