SumIF Relationship Between Tables

[cgoldstein]

Hello!

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.

I have tried Summarize, however keep getting "refers to multiple columns scalar expression errors"

I have (1) table that has EVERYTHING included in it. I am looking to create a 2nd table that includes the following:

I would like to group my large table by the following 5 items below, then create 2018 units and 2019 units.

-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

Existing Table

[v-lili6-msft · Accepted solution]

hi, @cgoldstein 

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.

Best Regards,

Lin