Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
Try this measure:
Running Total = VAR currentLeadTime = MAX('#Leads'[Lead time]) RETURN COUNTROWS(FILTER(ALL('#Leads'),[Lead time]<=currentLeadTime))
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |