How to show all rows including the 0 value rows in the aggregated table.

[xryu]

Hello,

We have 4 companies, i.e. A, B, C, and D.

We have a table that records the item sales amount of each company, sample is shown below.

SalesTable

Company	Amount
A	10
B	12
A	15
D	13
B	25
B	31
A	21
D	26
B	32
A	27

I need to calculate the total sales amount of each company and show in the result table.

Regardless the total sales amount is 0 or not, all companies are expected to show in the result table.

As shown in the below sample result table, total sales of Company C is 0, but it still shows up in the result.

Expected Result:

Company	Total Amount
A	73
B	100
C	0
D	39

I am implementing this aggregating calculation using the "Group By" in Power BI, however, Company C is NOT shown in the result table because the total sales amount is 0.

Result in Power BI:

Company	Total Amount
A	73
B	100
D	39

Is there a way that we can get the full company list with the aggregate sales amount, even if it is 0 in Power BI?

Thank you for your kindly help!

[amitchandak · Accepted solution]

@xryu , Create a company table with distinct companies and then join it back with company and use a measure with +0 

Company  = distinct(Table[Company]) // Make sure C is there , or bring from source

measure =  sum(Table[Sales]) +0

[xryu]

Hi amitchandak,

Thank you for the idea.

We were able to have Company C show up in the result table after left outer joined the company dictionary table to the aggregation result table, as you pointed out.

Somehow, the measure = sum(Table[Sales]) +0 did not work for us, but we were able to replace the null to 0

using the function in the Power Query Editor.

Thank you for your help, it really helped!

Bests.