How to group sales fact in table visual only?

[TM_AC]

Hi,

I have a sales fact table with fine granularity on 'posting date', 'item No.' level:



In powerbi I want to filter for January and the visual table should give me a result grouped by item No.:



I still want to be able to drill down to the 2 invoice rows of item 1550 with sold qty. of 10 and 17.
But at first sight the table should present the summed / grouped items.

Of course I can create a 2nd table in my model as a copy of the original one where I group by date and item. But this will almost double the amount of data in my model which I don't want.

2nd idea was to use a matrix. But with matrix I have the problem that I can only see the 'item No.' and no longer the 'item descr.' since it goes below the item No. in the matrix visual and only gets visible if I click the + symbol in front of the 'item No.'

How I can do that now?
Is there any way to group inside a visual only and keep the data on its fine granularity in the model?

[PC2790]

You can make use of matrix formatting options to get the items as per your requirement.

Like you can swith off the + sign and the stepped layout.

That way you will be able to see the columns and the functionality of drilling down will stay intact.

see this:

[TM_AC]

Hi PC2790,

thanks for reply, I could not really follow what you wrote.
But I found out more, my model (now) looks like this:


I added a table (not a matrix one) and if I keep the 'Posting date' column out of the table, the PowerBI sums up the rows by 'ItemNo' automatically:

...and it shows up 'ItemDescr' as well. So - without using a matrix table I am close to what I need.
My problem now is, how can I drilldown e.g. the item with 'ItemNo' 1550 to see the two lines which are summed up here?

This is the data behind the salesfact table:

In PowerBI how can I drilldown to see the lines from Posting date 2022-01-05 and 2002-01-07?

[PC2790]

In my earlier response, I suggested to use a matrix visual which you can customise to look like a table having the provision of drilling down at the same time.

You would not get the drill down functionality in a table as for drill down/up, a heirarchy is needed which the table doesn't have. 

Refer-Drill down and drill up in a visual - Power BI | Microsoft Docs

So, consider using a matrix and make use of the options shown above so that it looks similar to what you require.

[TM_AC]

Hi PC2790,

thanks for your further help. I think I could make it now:


There is only one problem left.
The final total shows 'Bolt' as ItemDescr, but in my opinion it should be empty since it's the total of all items and not just the one called 'Bolt'.
Is there any way to remove the 'ItemDescr' from the final total?

[PC2790 · Accepted solution]

Just make the option of Row Subtotal as off.