plot stack bar graph to visualize shift wise ordered data

[Anonymous]

I need to plot to shift-wise state change(green indicates RUNNING, red indicate STOPPED etc) distribution with time. I plotted below  stack bar with Jupiter notebook. But when I plot the same graph with powerbi(giving the same data) but In powerbi graph couldn't able get the original shift data order(ex:In original data set shows first green ,then Blue and green but powerbi shows it differently).

Is there any way to fix this issue?

Sample df:

Really appreciate your support !!!!!!!!!

[AllisonKennedy]

@Anonymous  How have you configured the stacked bar chart? Power BI will order by the fields used in the chart, you can use the 'sort by column' feature to help with this to some extent, but we need more info please.

[Anonymous]

after importing data to powerbi,it shows the wrong x-axis order(instead of 03-01 Night , graph shows 03-03 Day) and instead of real values it shows SUM. i marked both points in the attached image.

[v-kelly-msft]

Hi  @Anonymous ,

As tested here,it works fine:

Could you pls provide your .pbix file or sample data for test?Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

sure. 

Day-Shift State seconds
0 03-01 Day RUNNING 13711
1 03-01 Day STOPPED 84
2 03-01 Day RUNNING 16366
3 03-01 Day STOPPED 255
4 03-01 Day RUNNING 0
5 03-01 Day STOPPED 1
6 03-01 Day RUNNING 2
7 03-01 Day STOPPED 31
8 03-01 Day RUNNING 12751
9 03-01 Night RUNNING 31364
10 03-01 Night STOPPED 54
11 03-01 Night LOADING 5797
12 03-01 Night STOPPED 0
13 03-01 Night RUNNING 5985
14 03-02 Day RUNNING 881
15 03-02 Day STOPPED 15
16 03-02 Day BDT 270
17 03-02 Day STOPPED 0
18 03-02 Day RUNNING 1086
19 03-02 Day STOPPED 588
20 03-02 Day RUNNING 26997
21 03-02 Day STOPPED 63
22 03-02 Day RUNNING 12688
23 03-02 Day STOPPED 24
24 03-02 Day RUNNING 6
25 03-02 Day STOPPED 581
26 03-02 Night STOPPED 464
27 03-02 Night RUNNING 875
28 03-02 Night STOPPED 997
29 03-02 Night RUNNING 7440
30 03-02 Night STOPPED 10
31 03-02 Night BDT 310
32 03-02 Night STOPPED 0
33 03-02 Night RUNNING 33105
34 03-03 Day RUNNING 8668
35 03-03 Day STOPPED 13
36 03-03 Day BDT 430
37 03-03 Day STOPPED 0
38 03-03 Day RUNNING 14022
39 03-03 Day STOPPED 9
40 03-03 Day BDT 440
41 03-03 Day STOPPED 0
42 03-03 Day RUNNING 18838
43 03-03 Day STOPPED 780
44 03-03 Night STOPPED 43065
45 03-03 Night RUNNING 0
46 03-03 Night STOPPED 1
47 03-03 Night RUNNING 134
48 03-04 Day RUNNING 43163
49 03-04 Day STOPPED 37
50 03-04 Night STOPPED 2064
51 03-04 Night RUNNING 24711
52 03-04 Night STOPPED 8
53 03-04 Night LOADING 10242
54 03-04 Night STOPPED 0
55 03-04 Night RUNNING 6174
56 03-05 Day RUNNING 6116
57 03-05 Day STOPPED 629
58 03-05 Day RUNNING 1
59 03-05 Day STOPPED 23
60 03-05 Day RUNNING 2985
61 03-05 Day STOPPED 169
62 03-05 Day RUNNING 2490
63 03-05 Day STOPPED 12
64 03-05 Day BDT 340
65 03-05 Day STOPPED 0
66 03-05 Day RUNNING 4766
67 03-05 Day STOPPED 15
68 03-05 Day BDT 270
69 03-05 Day STOPPED 0
70 03-05 Day RUNNING 25385
71 03-05 Night RUNNING 10557
72 03-05 Night STOPPED 358
73 03-05 Night LOADING 3185
74 03-05 Night STOPPED 0
75 03-05 Night RUNNING 3614
76 03-05 Night STOPPED 237
77 03-05 Night RUNNING 25249

[v-kelly-msft]

Hi @Anonymous ,

As tested by your sample data,it works fine:

Pls pay attention that you have selected the rank order is by "Day-shift":

For details,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

No kelly, Please check the original dataframe. it's not correctly showing the state order. 

[v-kelly-msft]

Hi @Anonymous ，

I dont quite understand your point,could you pls make it more clearer?What is your expected output?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

hi Kelly, what I want to do is plot the stack bar chart but keeping data order correctly(without aggreagations).

For ex from above-given dataset: 

 03-01 Night RUNNING 31364
 03-01 Night STOPPED 54
 03-01 Night LOADING 5797
 03-01 Night STOPPED 0
 03-01 Night RUNNING 5985

 for the 03-01 Night shift stack should be beginnig RUNNING state with 31364 seconds,then STOOPED with 54 seconds, then LOADING, STOPPED, and RUNNING with coorespoing seconds but keeping data order as it is. In your asnwer it didnt show the exact data order. Please check your graph with dataframe.

[v-kelly-msft]

Hi  @Anonymous ,

Sorry for misunderstanding your point.

First create a column as below:

Column = 
var _rank=RANKX(FILTER('Table','Table'[Day-Shift]=EARLIER('Table'[Day-Shift])&&'Table'[combine]=EARLIER('Table'[combine])),'Table'[Index],,ASC)
Return
_rank&'Table'[State]

Then put the column as the legend of visual:

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Still, this is not the expected solution😕 X axis should be Day-Shift  and Y axis should be seconds but with the correct state order. and LEGEND SHOULD BE THE STATE(RUNNING,LOADING and etc Not the day specific legend as in your graph). Please check attached graph with question(RUNNING indicate with green , thats not only specific to particular day,that green color indicate RUNNING state for all the period)

[amitchandak]

@Anonymous , You can create an order/sort column for state and sort it on that. As State is legend and power bi do not support conditional formatting on the legend

refer for sort : https://www.youtube.com/watch?v=KK1zu4MBb-c

Conditional formatting (Do not work with legend) -https://www.youtube.com/watch?v=RqBb5eBf_I4