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

Question

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

Answer 1

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.

Answer 2

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

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

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.

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 (you can see by hovering mouse into Matrix visual).

• Drill Up – drill upper level after you drilled down Click to turn on

• Drill down – Turn on Drill down for drilling specific hierarchy

• Go to next level in the hierarchy – Parallel hierarchy drill down

• Expand all down one level in the hierarchy – Level by level hierarchy
  drilling