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 II
Super User II

Re: Summarize table depending on several fact tables

@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
Frequent Visitor

Re: Summarize table depending on several fact tables

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
Super User II
Super User II

Re: Summarize table depending on several fact tables

@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
Frequent Visitor

Re: Summarize table depending on several fact tables

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.

Highlighted
Solution Sage
Solution Sage

Re: Summarize table depending on several fact tables

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
Frequent Visitor

Re: Summarize table depending on several fact tables

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

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors