Forum Discussion

Raffael's avatar
Raffael
Frequent Visitor
8 years ago
Solved

Calculate Columns based on value comparison between two columns

Hello Power BI community,

 

i am struggling to accompish the following task: I want to sum revenue data based on the company family using DAX (would know how to do it in excel vba, but that feels too dirty...)

I have already checked this post, but the described solution is insufficient for my case.

E.G. I have the following data

company name    company family    revenue

comp a                 compfam a           100

comp b                 compfam b          200

comp c                 compfam a           200

 

and i already created a table with the distinct values from "company family"
company family     revenue

compfam a
compfam b

 

what i tried is

revenue = CALCULATE(SUM('Table1'[revenue]); 'Table1'[company family] = 'Table2'[company family])

but this does not work because of language restrictions (only one column per fitler expression)?

Does anyone know how to accomplish this task?

 

 

Regards,

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Raffael 

     

    If you want to create a summary table for the family from the source table, you can try the below DAX. Do it in Modelling -> nw Table and use this DAX.

     

    Sample2_Summary = SUMMARIZE(Sample2,Sample2[Company Family],"Total_Revenue",SUM(Sample2[Revenue]))

     

    But, you dont need to create this table unless you need it specifically as you can get the revenue per family in easy way.

     

    Create a measure Total_Revenue = SUM( Table1[Revenue]) and pull company family and this measure.

     

    Let me know if you need any further details.

     

    Thanks

    Raj

     

    Thanks

    Raj

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Raffael 

     

    If you want to create a summary table for the family from the source table, you can try the below DAX. Do it in Modelling -> nw Table and use this DAX.

     

    Sample2_Summary = SUMMARIZE(Sample2,Sample2[Company Family],"Total_Revenue",SUM(Sample2[Revenue]))

     

    But, you dont need to create this table unless you need it specifically as you can get the revenue per family in easy way.

     

    Create a measure Total_Revenue = SUM( Table1[Revenue]) and pull company family and this measure.

     

    Let me know if you need any further details.

     

    Thanks

    Raj

     

    Thanks

    Raj

    • Raffael's avatar
      Raffael
      Frequent Visitor
      8 years ago

      Thanks Raj, worked like a charme.