Forum Discussion

stiand's avatar
stiand
Regular Visitor
7 years ago
Solved

Create monthly overview

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. 

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    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-1122hAcB75Q3rLqG-Q_DQuVwTNFNoA?e=7mIeyW

     

    Best Regards,

    Amy

     

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

2 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    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-1122hAcB75Q3rLqG-Q_DQuVwTNFNoA?e=7mIeyW

     

    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's avatar
      stiand
      Regular Visitor
      7 years ago

      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.