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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
justinleow
Frequent Visitor

Running Total with category, can be shown in chart

Dear all, good day to you

I am very new to PowerBI, I us facing alot of roadblocks when trying to create a dashboard on this platform. I know my questions by be solved somewhere but there are so much similar solutions, I tried a couple but all does not seems to work for me. Seeking some of your advice to create a chart as such. I have a data set that I hope to calculate the running total count and percentage, so that I can display the distribution on a Bar in line combo chart.

Sample of the data as like this.

Shipment IDProductLead time
1Big2
2Big3
3Big4
4Big2
5Small5
6Big2
7Small3
8Small4
9Big2
10Small3
11Big4
12Small5
13Big1
14Small3
15Small4
16Small5
17Big1
18Big2
19Small3
20Small4
21Small1
22Small1
23Small2
24Small1
25Big2
26Big1
27Small3
28Big5
29Small4
30Big3


While I want to create a running total of lead times based on the ID count.

 CountCountCountRunning CountRunning CountRunning CountRunning Count %Running Count %Running Count %
Lead time (days)BigSmallAllBigSmallAllBigSmallAll
133633621%19%20%
2617941364%25%43%
32571192079%56%67%
424613132693%81%87%
5134141630100%100%100%
Grand Total141630      

 

Eventually to create a chart on power BI like this. When I slice it to "Big" or "Small", the bars and line will change automatically.
Chart.png

 

What I did not is to create a new table with "Summarize" of the Lead time, and then Calculating the running count with
A rankx calculation,

 

And then a calculate to calculate the cummulative count:
Count Running = CALCULATE ( SUM ('Lead Time'[ID Count] ), ALL ( 'Lead Time'), 'Lead Table'[Rank] <= EARLIER ('Lead Time'[Rank] ))

 

So all is ok to calculate the accumulative count and %, however, I am stuck to make this values dynamic to the products fields. I am very confused to the iterations functions and calculate functions like when to use SumX, Calculate, etc.  Really really apprecaite if you can give some guidance. 

Justin Leow

1 ACCEPTED SOLUTION
v-xjiin-msft
Solution Sage
Solution Sage

Hi @justinleow,

 

To achieve your requirement, I think you can try following measures:

 

Running Count =
CALCULATE (
    COUNT ( 'Lead Time'[Shipment ID] ),
    FILTER (
        ALL ( 'Lead Time' ),
        'Lead Time'[Lead time] <= MAX ( 'Lead Time'[Lead time] )
    ),
    VALUES ( 'Lead Time'[Product] )
)
Running Count % =
DIVIDE (
    [Running Count],
    CALCULATE (
        COUNT ( 'Lead Time'[Shipment ID] ),
        ALLEXCEPT ( 'Lead Time', 'Lead Time'[Product] )
    )
)

6.PNG

 

Thanks,
Xi Jin.

View solution in original post

2 REPLIES 2
v-xjiin-msft
Solution Sage
Solution Sage

Hi @justinleow,

 

To achieve your requirement, I think you can try following measures:

 

Running Count =
CALCULATE (
    COUNT ( 'Lead Time'[Shipment ID] ),
    FILTER (
        ALL ( 'Lead Time' ),
        'Lead Time'[Lead time] <= MAX ( 'Lead Time'[Lead time] )
    ),
    VALUES ( 'Lead Time'[Product] )
)
Running Count % =
DIVIDE (
    [Running Count],
    CALCULATE (
        COUNT ( 'Lead Time'[Shipment ID] ),
        ALLEXCEPT ( 'Lead Time', 'Lead Time'[Product] )
    )
)

6.PNG

 

Thanks,
Xi Jin.

Greg_Deckler
Super User
Super User

Try this measure:

 

Running Total = 
VAR currentLeadTime = MAX('#Leads'[Lead time])
RETURN COUNTROWS(FILTER(ALL('#Leads'),[Lead time]<=currentLeadTime))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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