Reply
Frequent Visitor
Posts: 2
Registered: ‎03-19-2017
Accepted Solution

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


Accepted Solutions
mim Member
Member
Posts: 71
Registered: ‎04-26-2016

Re: Best way to organize this data?

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

 

cheers

 

View solution in original post

Super Contributor
Posts: 1,199
Registered: ‎11-29-2015

Re: Best way to organize this data?

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.

----- Data Platform MVP (Power BI) -----

View solution in original post


All Replies
mim Member
Member
Posts: 71
Registered: ‎04-26-2016

Re: Best way to organize this data?

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

 

cheers

 

Super Contributor
Posts: 1,199
Registered: ‎11-29-2015

Re: Best way to organize this data?

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.

----- Data Platform MVP (Power BI) -----
Highlighted
Frequent Visitor
Posts: 2
Registered: ‎03-19-2017

Re: Best way to organize this data?

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.

 

 

Super Contributor
Posts: 1,366
Registered: ‎07-17-2016

Re: Best way to organize this data?

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