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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mladen
Regular Visitor

Power BI Basics - Column charts

Hallo Everybody,

 

I just started using Power BI to produce some basic reports for our Management, but I'm not really able to produce the desired Visualizations. It should be something very simple (it takes me couple of minutes in Excel)

 

I have a data set of ~1000 rows (employee names) and ~50 columns (employee charachteristics such as departments, locations, sale revenue in a specific year and product, etc.). I would like to use some of these columns as slicers / filters. (see below for table example).

 

Some of these columns are the 2012, 2013, 2014, 2015 and 2016 sale revenues of a product per empoloyee. How am I suppose to produce a simple stacked column chart, where on the horizontal axis I have years (2012, 2013, 2014, 2015, 2016) and vertical axis a column of total sales revenue ((stucked per product)? I would also like to use some of the previous 50 columns as Slicers (e.g. select only empolyee in a certain department or a specific location/s). 

 

In Excel I did the job by creating a Pivot Table, adding slicers, and creating a Pivot Chart. In Power BI I tried creating a new Table with DAX, but faild many times. Slicers based on the first table were not affecting my other tables neither...

 

Here a tabular explanation.

 

I would like to go from this table: --->

 

Employee IDLocationDepartment 2012 - product AAA - Sales
rev.
2013 - product AAA - Sales
rev.
2014 - product AAA - Sales
rev.
2015 - product AAA - Sales
rev.
2016 - product AAA - Sales
rev.
 2012 - product BBB - Sales
rev.
2013 - product BBB - Sales
rev.
2014 - product BBB - Sales
rev.
2015 - product BBB - Sales
rev.
2016 - product BBB - Sales
rev.
1USDep. A xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx
2EUDep. A xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx
3EUDep. A xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx
4USDep. B xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx
5AsiaDep. C xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx
  
1000EUDep. X xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx

 

--> ...to this one (and plot it, using slicer "Emploee ID", "Department", "Location"):

 Sales Revenue20122013201420152016
Product AAAxxxxxxxxxxxxxxx
Product BBBxxxxxxxxxxxxxxx
Product CCCxxxxxxxxxxxxxxx

 

Any kind of help or suggestion would be highly appreciated!!

 

Thank you very much and kind regards,

 

Mladen

1 ACCEPTED SOLUTION

Hi @Mladen,

The connection and slicers I used for Table1 are not working with NewTable. Do somebody know how to fix this?

Not like measures, calculate tables/columns don't respond to user selections on the report. So your new created table won't be affected by the slicers on the your report.

 

After reviewing your original dataset, I would suggest you use Unpivot option under Query Editor > Transform to unpivot your "2012 - product AAA - Sales", ... , "2016 - product BBB - Sales" columns first.

 

unpivot.PNG

 

Then, Split the "Attribute" column with "-".

 

splite.PNG

 

And Rename the columns like below.

 

rename.PNG

 

Last, you should be able to use a Matrix visual with "Product" as Rows, "Year" as Columns, and "Sales Revenue" as Values on the report. Smiley Happy

 

r2.PNG

 

Regards

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

You'll need to ensure that your tables are related to one another in your data model for the slicers from one table to affect the other. It sounds like you want a stacked column chart with Year as the horizontal axis, product as the legend and sales as the value.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Yeah, that's exacly what I would like to produce. Can someone tell me what a DAX for generating such a new table should be? Should I then estabish relationships between the tables manually?

Allright, I found a workaround to create the new table with:
 
NewTable =
UNION (
 ROW ( "Year"; 2012; "Sales Prduct A"; SUM(Table1[ProductAAA]; "Sales Prduct B"; SUM(table1[ProductBBB]))
 ROW ( "Year"; 2013; "Sales Prduct A"; SUM(Table1[ProductAAA]; "Sales Prduct B"; SUM(table1[ProductBBB]))
......
)
 
The connection and slicers I used for Table1 are not working with NewTable. Do somebody know how to fix this?
 
Thanks very much for your help guys! best regards,
 
Mladen

Hi @Mladen,

The connection and slicers I used for Table1 are not working with NewTable. Do somebody know how to fix this?

Not like measures, calculate tables/columns don't respond to user selections on the report. So your new created table won't be affected by the slicers on the your report.

 

After reviewing your original dataset, I would suggest you use Unpivot option under Query Editor > Transform to unpivot your "2012 - product AAA - Sales", ... , "2016 - product BBB - Sales" columns first.

 

unpivot.PNG

 

Then, Split the "Attribute" column with "-".

 

splite.PNG

 

And Rename the columns like below.

 

rename.PNG

 

Last, you should be able to use a Matrix visual with "Product" as Rows, "Year" as Columns, and "Sales Revenue" as Values on the report. Smiley Happy

 

r2.PNG

 

Regards

Hello,

 

Thanks very much for the tip. I think it goes in the right direction. Nevertheless I have more than 1000 rows (employees) and 30 columns (5 years x 6 products). When I unpivot, it increases the number of rows from 1000 to 30'000 (30*1000), it breakes other graphs I produced with my original dataset and I get errors in some rows (probably related to some errors in my original dataset). I'll try to produce the desired graph by cleaning my dataset first, then duplicate the query to preserve the original graphs and than try with the unpivot method. Hopfully it works!

 

Thank you very much for the support! It was very much appreciated!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.