Forum Discussion

PowerBrian's avatar
PowerBrian
Regular Visitor
4 years ago
Solved

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

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), co...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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's avatar
Seanan
Icon for Solution Supplier rankSolution Supplier
4 years ago

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's avatar
PowerBrian
Regular Visitor
4 years ago

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_productend_product
AA
AC
BE
CC
DF
AD
CA

 

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

 

results_table

productstart_product_countend_product_count
A32
B10

C

22
D11
E01
F01

 

 

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.

  • Seanan's avatar
    Seanan
    Icon for Solution Supplier rankSolution Supplier
    4 years ago

    Hi PowerBrian 

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

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

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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.