Dynamic Summary Table of a Field

[cjc322]

Hello,

Currently my fact table for a retail store consists of transactional data of lines of a SKU sales back to 2013. The fact table is 3,404,777 rows. This currently is the only data feed that I am receiving that contains SKU data. I am posting to see if anyone can suggest a method to create a seperate table that summarizes all the SKUs that are in the fact table; so that as a result I have just a seperate table of SKUs. The difficult part is I want this new table to be dynamic; meaning that each day when there are new transactions loaded to the fact table, those new SKUs that were not in the table would be added or somehow the whole table is recreated each day when the fact table is refreshed.

Please let me know if you need anymore info. Attahced is a screenshot of part of the fact table. the MerchID is the SKU.

Thanks!

 Chris

[Vvelarde · Accepted solution]

hi @cjc322

To create a summarize table of your SKus:

MOdeling-New Table

Merch_ID-Table = SUMMARIZE('Fact-Sales-Retail';'Fact-Sales-Retail'[Merch_ID])

[Anonymous]

Hi @Vvelarde

Is there anyway to pass slicer filter values to the summary table?

SUM_Tabl = Summarize('Fact','dimitem'[ItemName]),"Revenue",SUM('fact'[Revenue]))

fact has CompanyKey and joined with DimCompany.

I have slicer viz on the page Company from dimcompany.

when i select a company the SUM_Table should filter on that company.

somthing like: CALCULATETABLE( Summarize('Fact','dimitem'[ItemName]),"Revenue",SUM('fact'[Revenue])), filter(dimcompany,company=selectedcompany)).

i know my expression is wrong but kind of thing.! can we achieve?

[Vvelarde]

hi @Anonymous

Not tested yet but you can try it:

SUM_Tabl = Summarize(filter('Fact';fact[CompanyKey]=dimcompany[CompanyKey]),'dimitem'[ItemName]),"Revenue",SUM('fact'[Revenue]))