Forum Discussion

cjc322's avatar
cjc322
Frequent Visitor
10 years ago
Solved

Dynamic Summary Table of a Field

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

Need Help
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    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])

     

     

     

     

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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's avatar
      Anonymous
      Not applicable
      10 years ago

      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's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        10 years ago

        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]))