SumIF Relationship Between Tables

Question

Hello!&nbsp;I am needing help as I've tried many methods but am unable to get the modeling to take in PowerBI. Ultimately I am looking to create a subset of a table.&nbsp;I have tried Summarize, however keep getting "refers to multiple columns scalar expression errors"&nbsp;I have (1) table that has EVERYTHING included in it. I am looking to create a 2nd table that includes the following:&nbsp;I would like to group my large table by the following 5 items below, then create 2018 units and 2019 units.&nbsp;-Customer_cd (1)-Customer_nm (2)-Location_Channel_cd (3)-Location_Channel_Detail (4)-Location_Channel_nm (5)- New Metric 2018 Units = SUM([Units) where Fiscal_Year_nr = 2018- New Metric 2019 Units = SUM(Units)) Where Fiscal_Year_nr=2019&nbsp;Existing Table

v-lili6-msft · Accepted Answer

hi,&nbsp;Anonymous&nbsp;
Just try this formula to create a new table
New table =
SUMMARIZE (
    'Table',
    'Table'[Customer_cd],
    'Table'[Customer_nm],
    'Table'[Location_Channel_cd],
    'Table'[Location_Channel_Detail],
    'Table'[Location_Channel_nm],
    "New Metric 2018 Units", CALCULATE ( SUM ( 'Table'[Units] ), 'Table'[Fiscal_Year_nr] = 2018 ),
    "New Metric 2019 Units", CALCULATE ( SUM ( 'Table'[Units] ), 'Table'[Fiscal_Year_nr] = 2019 )
)
If not your case, please share some simple sample data and your expected output.
&nbsp;
Best Regards,
Lin

Forum Discussion

SumIF Relationship Between Tables

1 Reply

Related content

Relationship with Fact tables

Relationship

Relationship Issue

Relationships

Creating relationships