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
Anonymous
Not applicable

Displaying Barchart with Values per column with a Time X axis

Afternoon All,

 

Try to keep it brief but informative, having an issue where I want to display data over time (years) but the data I have received has, for example, column 1 named [2010 Sales) then another column [2011 Sales] and there is no actual Date column.

 

As each row will be a distinct product or product variation then columns to display sales in 20XX and any other data associated with that product.

 

Whereas the usual format we would expect is the [Date] column and a single sales column, then we use time intelligence to display sales per year, month, day etc.

 

Wondered if anyone has come across this issue, and how they overcame it.

 

Already tried to get another view of the data where an actual date would be used but this is not possible, unfortunately.

 

Thanks,

 

Josh

1 ACCEPTED SOLUTION

Here's what I think you're looking for. Take a look at the steps in PowerQuery for details on how I did it.  Essentially an unpivot and then a pivot.

 

https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-575635.pbix

View solution in original post

6 REPLIES 6
ssugar
Resolver III
Resolver III

Could you provide some sample data? 

 

Just off the top of my head, if you've got "2010 Sales" in one column, and "2011 Sales" in another column, you could look at unpivoting all the columns with the "#### Sales" in them, which would get you to a single column with those attributes.  Then you could split the column by space, and then you'd have a year column which could be used to create an actual date column.  Would need some sample data to get you a clearer answer.

Anonymous
Not applicable

So at the moment, the data is laid out like; 

 

Tax StatusRegionMinor RegionCountryCategory 1Category 2BrandQualityOriginLocal/ImportedVolume 2010Vol 2010Volume 2011Vol 2011Volume 2012Vol 2012Volume 2013Vol 2013Volume 2014Vol 2014Volume 2015Vol 2015Volume 2016Vol 2016Volume 2017Vol 2017USD Price 2011USD Price 2012USD Price 2013USD Price 2014USD Price 2015USD Price 2016Bottle Size 2010Bottle Size 2011Bottle Size 2012Bottle Size 2013Bottle Size 2014Bottle Size 2015Bottle Size 2016USD Value 2010USD Value 2011USD Value 2012USD Value 2013USD Value 2014USD Value 2015USD Value 2016
DomesticAfrica & Middle EastAfricaAngolaToysPlasticsSegaPremiumChinaImported          10.00000000.01000000.00000010.0000000      33.898722933.8987229     75.000000075.0000000     4.0678467534.06785E-08
DomesticAfrica & Middle EastAfricaAngolaToysPlasticsPlaystationUltra PremiumChinaImported        10.00000000.010000010.00000000.01000000.00000010.0000000     86.287658486.287658486.2876584    75.000000075.000000075.0000000    10.3545190110.354519011.03545E-07
DomesticAfrica & Middle EastAfricaAngolaToysPlasticsMicrosoftUltra PremiumAmericasImported        10.00000000.010000010.00000000.01000000.00000010.0000000     86.287658486.287658486.2876584    75.000000075.000000075.0000000    10.3545190110.354519011.03545E-07

 

and in an ideal scenario i could do with the layout been like; (manually done for row 1); 

 

Tax StatusRegionMinor RegionCountryCategory 1Category 2BrandQualityOriginLocal/ImportedDate (Year)VolumeValuePriceSize
DomesticAfrica & Middle EastAfricaAngolaToysPlasticsSegaPremiumChinaImported201410.00   
DomesticAfrica & Middle EastAfricaAngolaToysPlasticsSegaPremiumChinaImported201510.004.0733.8975.00
DomesticAfrica & Middle EastAfricaAngolaToysPlasticsSegaPremiumChinaImported20161.000.0033.8975.00

 

The actual source data is laid out like the example data though, does a pivot / unpivot do that... Not something ive done in PowerBI before but sounds like what i need. 

 

Thanks again, 

 

Josh

Here's what I think you're looking for. Take a look at the steps in PowerQuery for details on how I did it.  Essentially an unpivot and then a pivot.

 

https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-575635.pbix

Anonymous
Not applicable

Thank you very much! 

 

Now I've seen it, it seems so simple but honestly, I was scratching my head for hours!

 

Thanks, 

 

Josh

Happy I could help 🙂

Anonymous
Not applicable

Just give me 5 minutes and ill have an example data set, thank you in advanced. 

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.