Running Total with category, can be shown in chart

[justinleow]

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 ID	Product	Lead time
1	Big	2
2	Big	3
3	Big	4
4	Big	2
5	Small	5
6	Big	2
7	Small	3
8	Small	4
9	Big	2
10	Small	3
11	Big	4
12	Small	5
13	Big	1
14	Small	3
15	Small	4
16	Small	5
17	Big	1
18	Big	2
19	Small	3
20	Small	4
21	Small	1
22	Small	1
23	Small	2
24	Small	1
25	Big	2
26	Big	1
27	Small	3
28	Big	5
29	Small	4
30	Big	3

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

	Count	Count	Count	Running Count	Running Count	Running Count	Running Count %	Running Count %	Running Count %
Lead time (days)	Big	Small	All	Big	Small	All	Big	Small	All
1	3	3	6	3	3	6	21%	19%	20%
2	6	1	7	9	4	13	64%	25%	43%
3	2	5	7	11	9	20	79%	56%	67%
4	2	4	6	13	13	26	93%	81%	87%
5	1	3	4	14	16	30	100%	100%	100%
Grand Total	14	16	30

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.

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

[v-xjiin-msft · Accepted solution]

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] )
    )
)

Thanks,
Xi Jin.

[Greg_Deckler]

Try this measure:

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