Create Summarized Table from existing tables loaded in PowerBI

[Anonymous]

Hello,

I'm trying to figure out if there is a way to create a new table / data set from my existing data in PowerBI. For instance, right now I am loading several tables Invoice Detail, Date, Organization, SKU+DC Price Change table.

I really am looking for a subset of information for an ad-hoc analysis and would rather pull together using the existing relationships and export it out of PowerBI or create the calculations using this table (vs. all of the filtering and manuevering needing to be done on the whole model to get to my subset.)

How would I go about creating a new table based on distinct values? 

My invoice detail table for instance has

Day
Store
Product_id

Trxn Amount

In my final table I really want to look like this without duplicates

Product_id 
DC

Trxn Amount

Thank you!

[Greg_Deckler]

Use SUMMARIZE?

[Anonymous]

Thank you, I didn't know where to go to for the Summarize option and thought it was for a measure. However, I'm adding a new table with the below and keep getting "Invalid Identifier" error.

Q2 Price Window = CALCULATETABLE(
SUMMARIZE(
Invoice_Detail,
Invoice_Detail[Product_id],
Invoice_Detail[Organization_id],
Invoice_Detail[Product.ProductGroup_cd],
"TOTAL SALES", SUM(Invoice_Detail[Sales]),
"TOTAL UNITS", SUM(Invoice_Detail[Units]),
"TOTAL COGS STD", SUM(Invoice_Detail[GL_Cost_of_Sales_Standard]),
"TOTAL COGS FIFO", SUM(Invoice_Detail[GL_Cost_FIFO])
), 'Date'[Fiscal_year_nr] = 2018, 'Date'[Fiscal_quarter_cd]= "Q2")

[Greg_Deckler · Accepted solution]

Nope, you can create tables with it also. I'm thinking perhaps:

Q2 Price Window = CALCULATETABLE(
SUMMARIZE(
Invoice_Detail,
Invoice_Detail[Product_id],
Invoice_Detail[Organization_id],
Invoice_Detail[Product.ProductGroup_cd],
"TOTAL SALES", SUM(Invoice_Detail[Sales]),
"TOTAL UNITS", SUM(Invoice_Detail[Units]),
"TOTAL COGS STD", SUM(Invoice_Detail[GL_Cost_of_Sales_Standard]),
"TOTAL COGS FIFO", SUM(Invoice_Detail[GL_Cost_FIFO])
), 'Date'[Fiscal_year_nr] = 2018 && 'Date'[Fiscal_quarter_cd]= "Q2")

Or you could also do something like:

Q2 Price Window = SUMMARIZE(
FILTER(Invoice_Detail,'Date'[Fiscal_year_nr] = 2018 && 'Date'[Fiscal_quarter_cd]= "Q2"),
Invoice_Detail[Product_id],
Invoice_Detail[Organization_id],
Invoice_Detail[Product.ProductGroup_cd],
"TOTAL SALES", SUM(Invoice_Detail[Sales]),
"TOTAL UNITS", SUM(Invoice_Detail[Units]),
"TOTAL COGS STD", SUM(Invoice_Detail[GL_Cost_of_Sales_Standard]),
"TOTAL COGS FIFO", SUM(Invoice_Detail[GL_Cost_FIFO])
)

[Anonymous]

This worked! Thanks for your help!