Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
EnrichedUser
Helper III
Helper III

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

UserIDTransactionTypeLotNumberTransactionNumberQuantityBinIDLedgerDateTimeIndexLedgerDate
DC01500001846CPEREZORD.SHIP30000534-02-1C100280514-24<NONE>10/19/202036532110/19/2020
DC01500001853CPEREZORD.SHIP>KGK1BAAAC100253075-802<NONE>8/25/20202864778/25/2020
DC01500001853CPEREZORD.SHIP>60KFBDAAC100255963-19198<NONE>8/27/20202896918/27/2020
DC01500001853CPEREZORD.SHIP>20BGBDAAD1033372-39600<NONE>11/23/202043039411/23/2020
DC01500001853JOSERPHYSINV.POST>60KFBDAA00029711-3<NONE>11/22/202042953111/22/2020
DC01500001853QADGTW2INV.DISCREP  -3 11/26/202043528611/26/2020
DC01500001853QADGTW2INV.DISCREP  -3 11/28/202043530411/28/2020
DC01500001853QADGTW2INV.DISCREP  -3 11/30/202043532611/30/2020
DC01500001853CPEREZORD.SHIP>60KFBDAAD1033372-400<NONE>11/23/202043039511/23/2020
DA01500001854CPEREZORD.SHIP>S5J9BDAAD1024557-5<NONE>6/5/20201941106/5/2020
DC01500001854JOSERORD.SHIP>8GP1BAAA0002971136<NONE>11/22/202042953211/22/2020
DC01500001854QADGTW2ORD.SHIP  36 11/26/202043528711/26/2020
DC01500001854QADGTW2INV.DISCREP  36 11/28/202043530511/28/2020
DC01500001854QADGTW2INV.DISCREP  36 11/30/202043532811/30/2020
DC01500001881CPEREZORD.SHIP>01MFBDAAC100258529-8000<NONE>9/9/20203016659/9/2020
DA01500001881CPEREZORD.SHIP>TDF0BAAA00031146-3<NONE>1/13/20214942441/13/2021
DC01500001884CPEREZORD.SHIP>NR8QAAAAD1031203-25<NONE>10/14/202035887210/14/2020
DC01500001884JOSERPHYSINV.POST>1D6ZAAAA00030055-2<NONE>12/3/202044433212/3/2020

 

Expected Result:

 

ItemIDCount of ORD.SHIPRunning TotalHELPER - TOTAL SHIPPED% of Total
500001853441136.4%
500001854371163.6%
500001881291181.8%
5000018461101190.9%
50000188411111100.0%
50000188301111100.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") )

 

 

1 ACCEPTED SOLUTION
EnrichedUser
Helper III
Helper III

Figured it out

Pareto =
var ItemShips = [Lines Shipped]
var allships = CALCULATE([Lines Shipped], ALLSELECTED(ItemLedger) )


return
DIVIDE(
SUMX(
FILTER(
SUMMARIZE(ALLSELECTED(ItemLedger), ItemLedger[ItemID],
"T Shippo", [Lines Shipped]),
[T Shippo] >= ItemShips ),
[T Shippo] ),

allships, 0)

View solution in original post

1 REPLY 1
EnrichedUser
Helper III
Helper III

Figured it out

Pareto =
var ItemShips = [Lines Shipped]
var allships = CALCULATE([Lines Shipped], ALLSELECTED(ItemLedger) )


return
DIVIDE(
SUMX(
FILTER(
SUMMARIZE(ALLSELECTED(ItemLedger), ItemLedger[ItemID],
"T Shippo", [Lines Shipped]),
[T Shippo] >= ItemShips ),
[T Shippo] ),

allships, 0)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors