SUMX on summarized table

[Anonymous]

Hi,

I'm quite new in the usage of DAX. So sorry for this maybe basic question. Let's assume I have a fact table fallen 'Sales' which contains following columns:

Month, Country,Campaign,Product Code ,Units, Sales. 

Based upon this I am creating different measures. 

Now I would need to calculate an effect on a monthly basis by country and product (so practically without respecting the campaign column). This must be calculated row by row. So from my understanding it must be a SUMX function (but on a summarized / aggregated table).

Example:

The row below the countries are Campaigns on which the desired calculation 'Effect Region' is not possible (no correct Delta Units as PY figures are not on the same campaign) instead calculation should be done on the total of the market (so e.g. '1086*-0,32' for Czech Republic). The total overall selected markets should be the sum of every market ('-469' with this sample data)

My thinking was to apply SUMX on a summarized table so my measure for 'Effect Region' is this: = sumx(CALCULATETABLE(SUMMARIZE(Sales,Sales[COUNTRY_CODE],'Calendar'[Date],Sales[PROD_CODE],"Units summed",[UNITS Sum],"UNITS PY",[UNITS PY])),([Units summed]-[UNITS PY])*[Price Diff PY Region - Global]). But unfortunately there is no different result compared to = (SUMX(Sales,[Delta Units]*[Price Diff PY Region - Global])).

Is there any way in DAX to achieve the desired result?

Thanks a lot in advance 

Nico

[affan]

Hi @Anonymous,

Please share some base data and you expected outcome/result sample

Regards

Affan

[Anonymous]

Hi @affan,

thanks a lot for your suggestion. I created a sample. Values are different as in the screenshot as I created a smaller sample out of it (because of these ignore the errors in other measures - not affected by this problem - as well). My target: calculte as an 'Effect Region' on the country row [Delta units] * [Price Diff PY Region - Global], in the Total it should be the SUM of all selected Countries of the column 'Effect Region' (in the sample Czech Republic and Egypt together)

Sample SUMX Problem

Nico 

[Anonymous]

I am a little further now with the solution. But there is still one problem left which I don't understand. In the following example sample

I cannot add the measure 'Effect Product' to the existing table. It's running forever til the point I'm getting out of memory (if I'm not filtering it to very granular level before). Logically it's comparable with 'Effect Brand' which works technically without any issue.

Could someone explain me please why I have this problem with this measure and how I could improve it?

Thanks a lot in advance

NicoD82