How can I organizing stacked column chart in specific order?

[Anonymous]

Currently, my stacked column chart is visually organized from highest quantity to lowest quantity. I want it to be organized in a specific way. I want it to show: "Past due", Nov 2020, Dec 2020, Jan 2021, Feb 2021, Mar 2021, April 2021, "Future due". I tried creating a measure for each bucket, but it does not work in my case because there are new months being added and old months grouping in the "Past Due" bucket.

I tried sorting by a new column I created that shows order of the data using dates and that leads to a "Something went wrong error". 

I would really appreciate the help. Currently this is how Power BI has visualized the data. 

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ，

You want to sort your x-axis by "Past due", Nov 2020, Dec 2020, Jan 2021, Feb 2021, Mar 2021, April 2021, "Future due", am I right?

You can create a new sort table by the following measure:

Table 2 = ADDCOLUMNS(VALUES('Table'[ORDERING]),"forder",FIRSTNONBLANK('Table'[DUE_DATE],1))

Then you can sort the ordering column by forder column. At last, you can create a one to many relationship between sort table and data table by ordering column:

 

Use column in sort table as x-axis, it will show the order you expected.

For more details, please refer to https://radacad.com/sort-a-column-with-a-custom-order-in-power-bi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi@v-deddai1-msft ,

The Measure you created was not working for me. I have created a table below for sample of columns that might help us work this out. My goal is to replicate the "Ordering" column in the specific order it showing. We can use count of  "Buckets" for the y-axis.

Table1:

BUCKET	ORDERING	DUE_DATE
Past Due	Past Due	7/31/2020
Past Due	Past Due	10/31/2020
Past Due	Past Due	12/31/2019
11/1/2020	Nov 20	11/30/2020
11/1/2020	Nov 20	11/30/2020
11/1/2020	Nov 20	11/30/2020
12/1/2020	Dec 20	12/30/2020
12/1/2020	Dec 20	12/30/2020
12/1/2020	Dec 20	12/30/2020
1/1/2021	Jan 21	1/15/2021
2/1/2021	Feb 21	2/15/2021
3/1/2021	Mar 21	3/15/2021
4/1/2021	Apr 21	4/15/2021
More than 9 months ahead	More than 9 months ahead	dateadd(due_date,6,month)

[v-deddai1-msft]

Hi @Anonymous ,

What's you expected x-axis like? You can create a new table for x-axis:

Units =
UNION ( { "Past due" }, VALUES ( Table1[Unit] ), { "Future due" } )

Then you can use the following measure for your stacked column chart:

Measure =
SWITCH (
    SELECTEDVALUE ( Units[Value] ),
    "Past Due",
        CALCULATE (
            SUM ( Table[amount] ),
            FILTER (
                Table,
                Table[Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
            )
        ),
    "Future due",
        CALCULATE (
            SUM ( Table[amount] ),
            FILTER ( Table, Table[Date] > DATE ( 2021, 4, 30 ) )
        ),
    CALCULATE (
        SUM ( Table[amount] ),
        FILTER ( Table, TREATAS ( VALUES ( Units[Value] ), Table[Date] ) )
    )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

This is what this visual currently looks like.