Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
noob8
Frequent Visitor

Stacked Column X Axis

I have a sheet that tracks different accounts and their expiration dates. I have a calculated column within PowerBi, that calculates if the account is to expired within 1 month, 2 month, 3 month, 4 month, 5 month, 6 month, 7 month +. (For the purposes of this example, I have created this calculated column with my excel spreadsheet). The Sheet also includes additional information, such as Language, Region and Type. I have created a hirearchy as follows:

Region Hierarchy: Type, Region

 

I have a simple column stacked chart:

Axis:

- Expiration

- Region Hierarchy > Region

Legend: 

Region Hierarchy > Type

Value

- Count of Names

 

The data source only has information for accounts: that have already expired, or expiring within 1 month, 2 month, 5 months, 6 months 7 + months. 

 

Currently the X Axis in the chart only shows: 1 month, 2 month, 5 months, 6 months 7 + months. 

What I am hoping to see is the X Axis with: Expired, 1 Month, 2 Month, 3 Month, 4 Month, 5 Month, 6 Month, 7 + Month. Understanding that there would be 0 values on 3 month and 4 month.

 

Thanks for the help,

 

Sample Data:

datasource.PNG

 

Sample Graph:

Capture.PNG

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

 

just create a new table, that contains all the possible values.

Create a relationship between the new table (on the one side) and your existing table (on the many side), this article explains how to create relationships: https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

 

Use the column from the new table as axis, and make sure you enable this option "Show items with no data":

image.png

 

Hopefully this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

4 REPLIES 4
Cmcmahan
Resident Rockstar
Resident Rockstar

If you click the dropdown arrow on your axis field (Expiration in your case) you should see an option to Display Items with no data.  Selecting this will show you the expected blanks for month 3 & month 4.

 

After that, it's all about ordering your graph by X-Axis category.  It should be easy to get 1-7 months in order, with expired after 7, since that's alphabetical order.  If you want expired first, you'll need to set up an ordering column that indicates what order the categories should be sorted in and use that.

Hey @Cmcmahan ,

 

I assume that the current data does not contain the "missing" values, fo this reason I suggested to create a "dimension" table, we will see ...

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

You're right @TomMartens. Good catch.

 

Since these categories are treated as text by the Power BI engine, it is unaware that it should make a "3 months" and a "4 months" category on its own.   The benefit of creating the new table that lists each of the possible category options is that you can set up an index column and sort by that to get the desired order of categories on the X-Axis.

TomMartens
Super User
Super User

Hey,

 

just create a new table, that contains all the possible values.

Create a relationship between the new table (on the one side) and your existing table (on the many side), this article explains how to create relationships: https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

 

Use the column from the new table as axis, and make sure you enable this option "Show items with no data":

image.png

 

Hopefully this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors