Forum Discussion
Using aggregate (sum) in visuals is removing rows
Hello everyone! I've run into some behaviour in PowerBI I don't quite understand, which makes it difficult to figure out how to work around it. I have a table that has some master data for materials. Material number is unique in this table. Data looks something like:
| Material number | Name |
| AB1 | Product1 |
| CD2 | Product2 |
| EF3 | Product3 |
This table has a relation with a stock table.
| Material number | Amount | Location |
| AB1 | 1 | A |
| AB1 | 2 | B |
| CD2 | 3 | A |
Relation is 1 to n, with crossfilter direction single. There are no other tables related to either of these two tables.
Now I'd like to combine the Material number and Name from the material table, with the sum of the available stock from the stock table. What I'd want is a table visual that contains:
| Material number | Name | Sum amount |
| AB1 | Product1 | 3 |
| CD2 | Product2 | 3 |
| EF3 | Product3 |
|
What seems to be happening is the material numbers with no records in the stock table are no longer shown in the visual:
| Material number | Name | Sum amount |
| AB1 | Product1 | 3 |
| CD2 | Product2 | 3 |
How can I create a table visual that also shows the materials without stock?
Scala , Add +0 in your measure
sum(Table[Amount]) +0
or use the option in visual, show item with no data
2 Replies
Scala , Add +0 in your measure
sum(Table[Amount]) +0
or use the option in visual, show item with no data
Thanks very much!
