Create monthly overview

[stiand]

Hi,

I'm a newbie at PowerBI, and need some startup help for my data shaping. I have a table similar to the shown below (just a bit larger). What I want is to shape the data in order to be able to calculate total revenue, profit, etc. on each unit, and also to see the numbers for each month.

I have made some tests, but did not manage to get the data shaped correctly. I either got the data in the form where all numbers for unit A, B etc. where calculated in total, or with the transposed sheet where I got the revenue, profit etc. sum for all units 

Some basic guidance on how to shape the data would be very much appreciated. 

[v-xicai · Accepted solution]

Hi stiand ,

I create sample data to reproduce the scenario showing below picture ,setting the Item column has three instances as Revenue, Profit, Cost, and the Unit column has five instances from Unit A to Unit D ,when transfer your Excel format data to query format data in Query Edit.

 

The Matrix visual displays the whole data like below picture.

Then, create measure named "Sum Item" to calculate total Revenue, Profit, Cost, assuming the table named Table1.

Sum Item = CALCULATE(SUM(Table1[Value]),ALLSELECTED(Table1[Item]))

Finally, choose Clustered column chart visual to display the total Revenue, Profit, Cost for each month. For example, when make some selections in slicers, return the result showing below picture.

Here is my test pbix file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EcuHdbGGNHFJq5y-11...

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stiand]

Thanks for help!

The main problem I had was to pivot the columns properly. So for other newbies, the different pivotfunctions in queries are effective.