DAX measure across multiple tables

[jayjay0306]

Hi experts,

I hope you can help me with a challenge:

I have the following datamodel (simplified): 

In a report I can make a visualization which shows the following:

But I need to make a DAX measure, which shows the following:

Now, the example is simplified!

There is a twist: The problem is I need to sum it on customer BEFORE I sum the “sale per customer” by brand!!

Without adding the customer from the "customer dimension".

I have tried the following:

Sale by Brand = SUMX(SUMMARIZE(Customer,Customer[Customer Name],"Sale_",CALCULATE(SUM[Sale],CROSSFILTER('Product'[PRODUCT_KEY],'Sales North'[PRODUCT_KEY],Both)), CROSSFILTER('Product'[PRODUCT_KEY],'Sales South'[PRODUCT_KEY],Both))),[Sale_])

But the outcome is "100%" on all brands?

Can you crack the problem?

It will be much appreciated.

Br,

Jayjay0306

[jayjay0306 · Accepted solution]

thanks Fowmy, I did what you suggested and it works. 🙂

br,

Jayjay0306

[Fowmy]

@jayjay0306 

Sorry,  did not get it. Since you have the relationships done correctly, 

In a matrix, you can simply add product brand and the measure like 
Sales Measure = SUM( F_Slaes_North[Sales] ) + SUM( F_Slaes_South[Sales] ) 


Am I missing something here?

[Tom_Y]

@Fowmy I have a very similar problem, and I'm using your solution and it works. 

My very simple DAX: 

Total installed = [Count G]+[Count H]

Count G = COUNTROWS('Project G')

Count H = COUNTROWS('Project H')

And I have a "Project Dim" table recording all the Project Name, equal to their respective Table Name as well. All the Project Table is very simple with same structure.

Instead of 2 projects, the problem is we're going to have 20 projects, and new projects every month. My question is there any way to simply refer to this 'Project Dim' Table and get the name so I dont' need to hardcode it in DAX? (Or is it just normal practice in BI world, keep updating DAX for new project?) Thanks in advance.

[jayjay0306]

Hi Fowmy,

Yes, I understand your question, but this is the twist I am referring to. 

The problem is, that there may be - according to my datamodel - a Product_key column in the "F_Sales_South", but it has no key values. So the only sales split I can make on "F_Sales_South" is on "Customer_key". On "F_Sales_north", however, I have both Product_key and Customer_key on "F_Sales_North" (I know the "Sales_South" won't be split on brand, but I need the sales (volume) in the calculation anyway) .

Therefore, What I am trying to accomplish is somehow to:

1)  sum the "Sales South" + Sales North" to "Sales Total" by customer ID

2) split the "Sales Total"  by brand, using the product/customer relation, which I have in the "F_Sales_North" table.

If I combine the "Sales Total" with Brand and Customer in a visualization (plesase see my picture above), I get the correct result. But that is due to the Brand/customer context/combination in the visualization. I need the same result WITHOUT adding the customer ID.

Does it make sense? (I admit it is not 100% the logic I have shown in the data model, but I was hoping to get some "inspirational" replies, which I can use to solve the rest, due to the recognition, that the true setup is to hard to explain 🙂 ).

But if you have any ideas on how to solve this, it will be greatly appreciated.

thanks.

Br,

Jayjay0306   

[Fowmy]

@jayjay0306 

If you need to split the sales by brand then you need to have both the fact tables at the granularity of Customer and Product or at least the brand. in your case only one table has both and the other has only customer. 

How do you want the sales of F_Sales_South to split by Brand?I mean what should be the basis to split?

You may also share a dummy PBI file with data  along with the logic.