Forum Discussion

hidenseek9's avatar
hidenseek9
Icon for Post Patron rankPost Patron
9 years ago
Solved

Aggregate all the data to create a table

Hi, Power BI Community!

 

I am currently working on creating an income statement.

I have created a dummy data below to show you

what data I am working with.

 

Dummy Data

 

My Objective:

To create an income statement with a left hand side column that has

Item # and Item from "Master Filter" tab in month breakdown.

It would look like below picture.

 

 

Problems:

1. Struggling to get all the data in one table to create an income statement such as above.

2. Need to calculate Sales, Margin on Operations, Net Contributions and Profit within Power BI

  • Sales = Volume (Demand tab) * Sales/KG (CANNKG tab)
  • Margin on Operations = Sales - sum(Material, Industrial, Logistics)
  • Net Contributions = Margin on Operations - A&P
  • Profit = Net Contributions - sum(Sales Force, OH, Others)

I think it is not diffucult at all to do above but I just cannot figure out how to do this....

 

Any suggestion would help me greatly.

 

Appreciate the support!

 

H 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    HI hidenseek9

     

    The easiest approach to achieve the visual below is to organise all your data into a single table with a structure similar to this

     

    Date       , Item Name , Value
----------------------------------
    1-jan-16   , Volume   , 100
    1-Feb-16   , Volume   , 120
    1-Mar-16   , Volume   , 100
    ....
    1-Dec-16   , Volume   , 150
    1-Jan-16   , Sales    , 200
    etc
    etc


    Then you can create measures to suit using the CALCUATE and FILTER functions eg

     

    Sales = CALCULATE(SUM([Value]),FILTER('table',Item='Volume') * ......

3 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    HI hidenseek9

     

    The easiest approach to achieve the visual below is to organise all your data into a single table with a structure similar to this

     

    Date       , Item Name , Value
----------------------------------
    1-jan-16   , Volume   , 100
    1-Feb-16   , Volume   , 120
    1-Mar-16   , Volume   , 100
    ....
    1-Dec-16   , Volume   , 150
    1-Jan-16   , Sales    , 200
    etc
    etc


    Then you can create measures to suit using the CALCUATE and FILTER functions eg

     

    Sales = CALCULATE(SUM([Value]),FILTER('table',Item='Volume') * ......