column chart with both X axis levels one next to the other

[TasosSTV]

Hello,

I need to create a visualization that I don't know how to do it and I haven't found a similar topic.

I have created a legend based on movement status. In the screenshot below, there are three columns (X-axis is the movement status and value is 1).

level1

However, next to these three, I want to add a fourth column (let's call it mixed), if a movement has more than one statuses (e.g. I have three orders for the same product to three different destinations. Two of them are accepted and the third is still pending). I have created a new table with one column and one value (i.e. Mixed) and added it to the Axis field.

level2

However, when I try to show both, PowerBI gives me the graph below instead of the one I want. 

Level1&2

Any help is more than welcome.

Regards

[neha369]

You should be having all the status in one column i.e accepted, Open, Pre allocated and mixed . Instead of creating a table. create one column of actual status and define what movement describe what status. And then you can use "Actual status in your axis. "

[TasosSTV · Accepted solution]

Hello Neha,

Thank you for your answer.

The issue is that the "mixed" status, doesn't really exist in my data. It is a notional one based on the scenario I explained above.

I believe I have managed to do what I was looking for. I firstly created a summarised table (based on the product description in my case). Following that, I created a new column called "NewStatus". If there are orders for the same product with only one status, then I return this value. Otherwise, it returns the word "Mixed" (code below).

NewStatus = IF(CALCULATE(DISTINCTCOUNT(Movements[Status]))=1,FIRSTNONBLANK(Movements[Status],Movements[Status]),"Mixed")

Thanks for your help though.