Matrix subtotals - is it possible to replicate tabular-style Excel pivot table?

[leightx]

Hello! I might be going about this all wrong, but I'm looking for a visual that allows me to basically get the same data as a pivot table in excel. I'm looking for a tabular layout with subtotals, without the item names repeated. The closest I can figure is using a matrix, expanding all the rows, and turning row subtotals on. But then the subtotals are in the "wrong" place. Or rather, Power BI is subtotaling ALL of the items.

So for example, if 1 customer has 2 orders, I would like the subtotal for EACH order when I turn on the order subtotal.  Instead, I get the subtotal for ALL orders for that customer. The only way to get the subtotal per order is to turn on the subtotal for the next row in the matrix (in my case, the date). This seems really counterintuitive. Additionally, this means that I can't put the subtotal row in the expected location (under the thing being subtotaled, rather than one "column" in from that). Am I doing something wrong?

 

[jdbuchanan71]

Hello @leightx 

I agree it is a bit confusing that subtotal on a column is "Subtotal of all" rather than "Subtotal on each".  To follow on to what @MFelix  wrote, you can also rename the subtotal you display under date to say "Order Total" or something similar.

[leightx]

Hello! I think that seems to be the only solution. It just seems odd that this is the intended (??) behavior for subtotals!

I am doing everything as suggested above. See screenshots for the format info. My workaround is exactly what you suggested - subtotal the date row and call it Order Totals - which seems a bit backwards. 

[MFelix]

Hi @leightx ,

You need to use the matrix visual as you are refering and the go to sub-total and select the Per row level.

Disable every row except the Date column, this is the level in wich you want to see the order total, you are not saying that the sub-total is made on Date but on the column of the date so it will sum up all columns until the date. May seem a little odd but the the idea is to give the total per each column level. this can be checked if you select the Customer level will give you overall total,

Check PBIX file attach.

Regardss,

MFelix

[parry2k]

@leightx seems like something is not right, can you share what you have put on rows, columns and values in your matrix visual?

[leightx]

Another bit of odd behavior - the subtotals are duplicated under column heading that do not have subtotals (see screenshot above, where order total is duplicated for each customer). I was able to remove the duplicated subtotals by clicking the - button, but it seems like a bug.  

[MFelix]

Hi @leightx ,

what is the version you have PBI? 

the latest error you refer to I cannot replicate.

Regards,

MFelix

[leightx]

So this is very odd, but today it seems to be working as it should. I'm not sure if PBI updated overnight? I had already closed  the pbix and restarted the computer yesterday, so I don't think that was the issue. Anyway, it appears that the subtotals are now in the correct place!