Hello,
I am new to PowerPivot and BI. Here is a sample data table which I will call TABLE. The Calculated Field is what I am trying to fill in. I hardcoded the expected values that I would expect from my calculated field.
[TABLE="width: 429"]
<tbody>[TR]
[TD]Campaign_ID[/TD]
[TD]Location[/TD]
[TD]Amount[/TD]
[TD]Calculated Field (expected Value)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mexico[/TD]
[TD]5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mexico[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]US[/TD]
[TD]10[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]US[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]US[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Canada[/TD]
[TD]5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Germany[/TD]
[TD]5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Brazil[/TD]
[TD]5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Canada[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want to calculate a new field where I sum up all the amount values for a given campaign ID. In simple Excel, it would be SUMIF Amount where Campaign=1 or equal to 30 that waterfalls down the table.
I can then divide the amount for a given row by the calculated field to get the weight of each location for a given campaign ID. For example, Mexico has a weight of 5/30 for campaign_id=1 and US = 10/30 for campaign_id=1, etc. etc. (There is a deeper calculation off this calculated weight that I won't elaborate on).
First, I created a measure called Total Amount as Total Amount = sum([Amount])
I then created the following DAX Formula as a Calculated Field:
=CALCULATE ([Total Amount],ALL(TABLE,[Campaign_ID]))
When I use this formula, I only get the value for each cell, not the sum of the array for the campaign row value that I am on.
I have trade all sorts of flavors of FILTER, CALCULATETABLE, etc and I either get the sum of the entire array or and ERROR message.
Any idea where I am going wrong? I am perplexed because it appears to me to be a simple sumif in excel for on each campaign ID down the table.
I am new to PowerPivot and BI. Here is a sample data table which I will call TABLE. The Calculated Field is what I am trying to fill in. I hardcoded the expected values that I would expect from my calculated field.
[TABLE="width: 429"]
<tbody>[TR]
[TD]Campaign_ID[/TD]
[TD]Location[/TD]
[TD]Amount[/TD]
[TD]Calculated Field (expected Value)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mexico[/TD]
[TD]5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mexico[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]US[/TD]
[TD]10[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]US[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]US[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Canada[/TD]
[TD]5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Germany[/TD]
[TD]5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Brazil[/TD]
[TD]5[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Canada[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want to calculate a new field where I sum up all the amount values for a given campaign ID. In simple Excel, it would be SUMIF Amount where Campaign=1 or equal to 30 that waterfalls down the table.
I can then divide the amount for a given row by the calculated field to get the weight of each location for a given campaign ID. For example, Mexico has a weight of 5/30 for campaign_id=1 and US = 10/30 for campaign_id=1, etc. etc. (There is a deeper calculation off this calculated weight that I won't elaborate on).
First, I created a measure called Total Amount as Total Amount = sum([Amount])
I then created the following DAX Formula as a Calculated Field:
=CALCULATE ([Total Amount],ALL(TABLE,[Campaign_ID]))
When I use this formula, I only get the value for each cell, not the sum of the array for the campaign row value that I am on.
I have trade all sorts of flavors of FILTER, CALCULATETABLE, etc and I either get the sum of the entire array or and ERROR message.
Any idea where I am going wrong? I am perplexed because it appears to me to be a simple sumif in excel for on each campaign ID down the table.
