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), count('end_product').  This seems like it should be really simple, but I can't figure it out.

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.

4 Replies

  • 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.