Trying to aggregate values from 2 different fields in the same table

[PowerBrian]

I have a fact table that contains a column named 'start product' and a separate column named 'end_product'.  I want to return a separate table with 3 columns: 'product_name', count(start_product), count('end_product').  This seems like it should be really simple, but I can't figure it out.

[Seanan]

Hi @PowerBrian 

A similar post here should be able to help you. when it comes to counting the start and end product you can use a simple measure to get the counts:

Create 2 of these measures, 1 for start_product and 1 for end_product.

CountSales = COUNT(SalesTeam[Sales])

 If you need any extra help please let me know.

[PowerBrian]

Thanks for the response.  I think the thing that is confusing me is that the values are in the same table.  As an example, my fact table would contain the following:

fact_table

start_product	end_product
A	A
A	C
B	E
C	C
D	F
A	D
C	A

The results that I would expect in my new table would be the following:

results_table

product	start_product_count	end_product_count
A	3	2
B	1	0
C	2	2
D	1	1
E	0	1
F	0	1

I want to be able to pull all of the product types from the start_product column and all of the products from the end_product column and aggregate them into a single list.  After the list is created, I would like to have the total number of instances for each in separate columns.

[v-zhangti · Accepted solution]

Hi, @PowerBrian 

Please try the following methods.

Table:

results_table = 
DISTINCT(UNION(VALUES(fact_table[start_product]),VALUES(fact_table[end_product])))

Manually change one of the following names.

Column:

start_product_count = 
CALCULATE (
    COUNT ( fact_table[start_product] ),
    FILTER ( 'fact_table', [start_product] = EARLIER ( 'results_table'[product] ) )
) + 0

end_product_count = 
CALCULATE (
    COUNT ( fact_table[end_product] ),
    FILTER ( 'fact_table', [end_product] = EARLIER ( 'results_table'[product] ) )
) + 0

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Seanan]

Hi @PowerBrian 

Would it be possible for you to provide the PBIX file?

Please make sure there is no sensitive data in the file.