Forum Discussion

Markando's avatar
Markando
Frequent Visitor
8 years ago

Summarize table depending on several fact tables

I have built a table containg all active teams and their corresponding sales, shipments and cases which works fine. The goal is to create a table with only active teams which I can then use as a slic...
Need Help
vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
8 years ago

Markando where are you summarising at the moment, in a new table?  or not ? sorry it would be best if i could see what you were actually doing? and the error or result you getting

 

 

Markando's avatar
Markando
Frequent Visitor
8 years ago

Hi

 

Im creating a new table. Let me try to show with a detailed summary:

I got five tables as below:

 

TEAM 
CodeName
1A
2B
3C
4D
  
CUSTOMERS 
CustomerCodeTeamCode
11
22
33
44
  
Sales 
CustomerCodeAmount
11000
  
Shipments 
CustomerCodeShipments
2400
  
Cases 
CustomerCodeShipments
35000

 

 

What I want to do is create a new table with all teams that has been active in either of the Sales, shipments or cases table. The primary key in these tables are customerCode. All CustomerCode is related to a single teamcode. 

 

My DAX above works perfect and I get this table:

 

TeamCodeTeamNameSUM OF SALESSUM OF CASESSUM OF SHIPMENTS
1A1000  
2B  400
3C 5000 

 

 

However I would like the same table, but withouth the summing. If I remove the sums I get a summarize over the entire team-table and thus also showing team D.

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hi Markando,

     

    Why don't you want to use SUMMARIZE()? 

     

    Per my understanding about your issue, the SUMMARIZE() should be the easiest way to achieve your requirement since you don't want to use Power Query.

     

    Thanks,
    Xi Jin.

    • Markando's avatar
      Markando
      Frequent Visitor
      8 years ago

      Hi

       

      I'm more then happy to use summarize()! However my question is how do i use a filter in either summarize or summarizecolumns to get a list as below depending on all the team-values that exist in either of the three fact tables