Sumproduct when no value is present in the table

[Essevee]

I have a table looking like this:

Product; Amount (tons);Sales Location; %of product sold in that location

1;100;Europe;4

1;100;NorthAmerica;60

1;100;SouthAmerica;36

2;50;Europe;20

2;50;Africa;40

2;50;Asia;40

--> now I want to make a graph with "sales location" as dimension, and as measure "sum(Amount*%of product)"

--> eg to calculate the weight sold in each location. So should be 4% *100 + 20% * 50 for Europe eg.

However, I do not get a value for eg. North America since no % is found for product 2. (In case the region is not in list for a certain product it means that the % sold there = 0).

Can someone help ?

Thanks !

[sunny_talwar]

I am not seeing any issue when I try to use Sum([Amount (tons)] * [%of product]) with the data provided.

[dwforest]

You could try adding + Sum({1} 0) to the formula and see if that helps  and/or check the setting under Add-Ons->Data Handling->Include Zero Values

[Essevee]

seemed that my example was too simple for what i actually wanted to do.

Hereby my load script: 

table1:

product-nr

product-category

total wet weigth (//in tons)

table2: 

product-nr

contentName (//eg H2O,sand,metal)

content%

And I want to plot following graph:

-  dimension = product category

 - measure = sum ( ([TotalWetWeight])*(100-[Content](--> when contentname=H2O))/100*([Content] (when contentname=metal)) )

[sunny_talwar]

Would you be able to share some data and the output you expect to see from it?

[Essevee]

see attached (is this format ok, or do you prefer excel input?)

graph on the right is the desired one (but data is summed wrong ! )

graph on the left has the right data (but not the desired dimension)

[sunny_talwar · Accepted solution]

How does this look?

If it is, then try this

Sum(Aggr(

    (sum([wetweight (tons)])
    -
    Sum({<Contentname={'H2O'}>}[wetweight (tons)]*[Content%])/100)
    *
    sum({<Contentname={'sand'}>}[Content%]/100)

, productnr, category))

[Essevee]

Very nice !

Looks perfect. Thanks a lot !