Aggregate all the data to create a table

[hidenseek9]

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 

[Phil_Seamark · Accepted solution]

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') * ......

[hidenseek9]

@Phil_Seamark

Thank you for your reply!

I think I can make it work.

Many thanks,

H

[Phil_Seamark]

Let me know how you get on. 🙂