How to add a new colum in power pivot under some conditions

[Schwinn123]

I m a new user for Power BI, I have encountered an issue which is I want to add a new column which the data comes from other 4 tables.

I have Table A, Table B, Table C, Table D and Summary Table.   In table A B C D, each of them has 2 columns called [Average] and [EMB]  And I also created 4 Measures called ratio A,ratio B,ratio C,ratio D, In summay table I want to create a new column, the logic is like:

EMB = IF('Table A[Average]=0,0,'Table A[EMB]*ratio A)+IF('Table B[Average]=0,0,'Table B[EMB]*ratio B)+IF('Table C[Average]=0,0,'Table C[EMB]*ratio C)+IF('Table D[Average]=0,0,'Table D[EMB]*ratio D)

While its is a just "Excel" formula, I have no idea how to perform this in DAX formula. I tried to use IF funtion as shown above, but Table A doesn't show up when I press " ' ".  

Can any body have some ideas with this.

[v-shex-msft]

HI @Schwinn123,

You can try to use following calculate table formula to get summarize result based on four tables:

Table =
ADDCOLUMNS (
    UNION (
        SUMMARIZE ( TableA, "Ratio", [Ratio a] ),
        SUMMARIZE ( TableB, "Ratio", [Ratio b] ),
        SUMMARIZE ( TableC, "Ratio", [Ratio c] ),
        SUMMARIZE ( TableD, "Ratio", [Ratio d] )
    ),
    "New", IF ( [Average] = 0, 0, [EMB] * [Ratio] )
)

If above not help, can you please share some sample data for test?

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[venal]

@Schwinn123 

Power BI Desktop >> select "Summary Table" from Fields pane >> Click on "New Column" (Home) and paste the query.

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

If this post was helpful may I ask you to mark it as solution and give it some kudos?