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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

Top Solution Authors