locked
Total Operating Expense, Margin, etc RRS feed

  • Question

  • I've been reading about cascading subtotal, but how can I create things like Total expense (which doesn't work because it is the combined amount of two subtotals) and things like ratios (gross margin etc)? 

    Thanks


    Friday, November 4, 2016 11:29 PM

Answers

  • You need to learn to write some dax measures. Think of a measure as a formula in a cell in Excel. Write a measure for example (use your own column names) Total Sales = sum(table[sales]) Total cost = sum(table[cost]) Gross Margin = [Total Sales] - [Total Cost]

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Saturday, November 5, 2016 4:40 AM
    Answerer
  • Oh, you want an inocme statement or similar. Watch this video by my colleague Derek. https://youtu.be/ojHZkWkEY7Q

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au



    Saturday, November 5, 2016 3:18 PM
    Answerer
  • Hi AlexMartini,

    Sorry for inconvenience, could you please post some same sample data and what you want clearly for analysis? I understand you should protect the data. While I could appreciate it if you could make up some date, and post the expected result like the thread


    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 16, 2016 8:31 AM

All replies

  • You need to learn to write some dax measures. Think of a measure as a formula in a cell in Excel. Write a measure for example (use your own column names) Total Sales = sum(table[sales]) Total cost = sum(table[cost]) Gross Margin = [Total Sales] - [Total Cost]

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Saturday, November 5, 2016 4:40 AM
    Answerer
  • Hi Matt, 

    Yeah, I understand that, but how do you get that to appear in a financial statement as a row header? The point of cascading subtotals is that you can add rows that get you to things like gross profit, and operating profit, and it fits nicely within the same pivot table. However, as I mentioned, it's not clear how to do the same for things like gross profit margin or total operating expense. Thanks. 

    Saturday, November 5, 2016 7:07 AM
  • Oh, you want an inocme statement or similar. Watch this video by my colleague Derek. https://youtu.be/ojHZkWkEY7Q

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au



    Saturday, November 5, 2016 3:18 PM
    Answerer
  • Thanks, I'll check it out. 

    Monday, November 7, 2016 7:04 PM
  • Hi Alex,

    Ok, in order to help more people, please mark the solution as answer, or share your solution if you have resolved your problem. Thanks a lot.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 10, 2016 1:36 AM
  • Hi Matt. That was a very good video. Had to go through it slowly but it stuck. However, it still doesn't give the solution with regard to coming up with subtotals for things like TOTAL OPERATING EXPENSE which is a subtotal of two expense sections. The video and most other resources out there only relate to cascading subtotals. 
    Tuesday, November 15, 2016 12:04 AM
  • Hi AlexMartini,

    Sorry for inconvenience, could you please post some same sample data and what you want clearly for analysis? I understand you should protect the data. While I could appreciate it if you could make up some date, and post the expected result like the thread


    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 16, 2016 8:31 AM