Calculate Columns based on value comparison between two columns

[Raffael]

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 · Accepted solution]

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]

Thanks Raj, worked like a charme.