cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 slicer and make it a bit more user friendly

 

However it would be even better if I didn't have to make my calculations (SUMx3), but I can't get it working when the team code is in three different fact tables. Do anyone know how I can recreate  this table with only all distinct Team[Code] that is available in the three fact tables?

 

My current DAX-code:

 

SUMMARIZECOLUMNS(

Team[Code];Team[Name];
"SUM OF SALES";calculate(sum(Sales[SalesAmount]));
"SUM OF CASES";calculate(countrows('Cases'));
"SUM OF SHIPMENTS";calculate(sum(Shipment[Shipments]))

)

6 REPLIES 6
Highlighted
Super User I
Super User I

@Markando you could create one table with the combined code values create a relationship between all the tables and the new lookup table and then summarize?

 

be best do it in power query / query editor.

 

duplicate all the tables, remove everythign except the code column, append them together and then deduplicate, add it to your model and create a relationship.  not sure if that will do it but it might





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Hi Vanessafvg, thanks for your reply. 


The powerquery works fine, however I would prefer to do it in dax. I know how I put in a table in the summarizefunction, however for this case i need to build a new tablefunction containg the distinct team code value from three different fact-tables, its there I'm currently stuck. 

Highlighted

@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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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.

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.

Highlighted

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

 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors