Forum Discussion
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
- vanessafvg
Community Champion
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
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.- vanessafvg
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
