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
jmclaren
New Member

Best way to organize this data?

Hello everyone,

 

I am trying to sort this data so that it can easily be read by Power BI. Since each month has it's own column i cannot get visualizations to properly display information that i need.  My objective is to show our current sales compared to goal over time (for each month).  Any advice would be appreciated.

 

2017-03-19_18-57-28.jpg

2 ACCEPTED SOLUTIONS
mim
Advocate V
Advocate V

use powerquery, select the first column and unpivot the rest.

 

cheers

 

View solution in original post

Phil_Seamark
Employee
Employee

Hi @jmclaren

 

You will find Power BI much happier if you completely pivot your data to the following structure

 

Group   , Month    , Sales , Goal  
-----------------------------------------------
Group 1 , 1/1/2017 , 1234 , 5555
Group 2 , 2/1/2017 , 3423 , 6666
Group 3 , 1/1/2017 , 3432 , 3423
etc....

 

You will be able to add a Progress calcualted column using a formula based on [Sales] and [Goals]

 

But other measures will be easier to build.

 

Even with the data in the format I suggested, you will still be able to create a matrix using the format in your image if needed.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
jmclaren
New Member

Guys, thank you so much.  That helped.

 

Any of you interested in helping me out remotely with some simple power BI reports?  I have a few reports i would like to finish before the end of the week and it would go a lot faster if i had someone experienced.  $100 per hour.

 

 

Hi @jmclaren,

 

Could you accept the helpful reply as solution to help others who may have similar issue easily find the answer and close this thread?Smiley Happy

 

Regards

Phil_Seamark
Employee
Employee

Hi @jmclaren

 

You will find Power BI much happier if you completely pivot your data to the following structure

 

Group   , Month    , Sales , Goal  
-----------------------------------------------
Group 1 , 1/1/2017 , 1234 , 5555
Group 2 , 2/1/2017 , 3423 , 6666
Group 3 , 1/1/2017 , 3432 , 3423
etc....

 

You will be able to add a Progress calcualted column using a formula based on [Sales] and [Goals]

 

But other measures will be easier to build.

 

Even with the data in the format I suggested, you will still be able to create a matrix using the format in your image if needed.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

mim
Advocate V
Advocate V

use powerquery, select the first column and unpivot the rest.

 

cheers

 

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.