How do I create a report with subtotals and a grand total in Power BI?

5
votes

Currently, I'm using the Power BI Designer (newest version). I'm completely new to it. I'm wondering how to get the subtotal of a group of items and then get the report grand total. I only need the subtotal for one column (ie. price). The grouping will be on 3 columns (date, location, itemname).

Power BI is connected to a Azure SQL database (not SSAS).

2
reportbusiness-intelligencesubtotalpowerbi

2 Answers

4
votes

Update: Matrix is now supported in both Power BI Desktop and PowerBI.com.

Just add the 3 levels of grouping and the measure you want. The subtotals will show no problem.

2
votes

As Lukasz suggesting, You can use Matrix visual in Power BI for achieving this requirement.

enter image description here

Find the option to enable/disable Sub-Total and Grant Total option in Format tab of the matrix visual.

enter image description here

Sample Matrix visual,

  • Red color highlighted Total is Sub-Total.
  • Blue color highlighted Total is Grant-Total.
  • Green color highlighted as hierarchy option for Matrix visual.

enter image description here

Some of the Matrix visual features are explained below,

  • To expand the next level of the hierarchy, you have to click the hierarchy icon available in the top right corner of the visual enter image description here (you can see by hovering mouse into Matrix visual).

  • enter image description hereDrill Up – drill upper level after you drilled down Click to turn on

  • enter image description hereDrill down – Turn on Drill down for drilling specific hierarchy

  • enter image description hereGo to next level in the hierarchy – Parallel hierarchy drill down

  • enter image description hereExpand all down one level in the hierarchy – Level by level hierarchy
    drilling