Column total

[kizofilax]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]2011[/TD]
[TD]2011-Ratio[/TD]
[TD]2012[/TD]
[TD]2012-Ratio[/TD]
[/TR]
[TR]
[TD]system[/TD]
[TD]12[/TD]
[TD]12/34[/TD]
[TD]50[/TD]
[TD]50/80[/TD]
[/TR]
[TR]
[TD]board[/TD]
[TD]22[/TD]
[TD]22/34[/TD]
[TD]30[/TD]
[TD]30/80[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD]34[/TD]
[TD][/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am trying to calculate the Ratio columns. Basically my problem is to create a measure for each row that is the total of the column and using it as a divisor on each row. If I only have 1 year I can accomplish this with

=CALCULATE( SUM([Sum] ), ALLSELECTED( )) but as soon as I add another year this doesn't work

Any ideas?

Thanks

 

[RoryA]

Can't you just choose to display the values as a percentage of the column in the pivot table rather than using a measure?

 

[Laurent C]

The problem with your calculation is that SUM([Sum]) will be evaluated for the whole pivot table, so get a table grand-total.

This being said, you cannot create a measure that says "Total of the column". You can, however, calculate the total for all selected products, or for the currently selected year, ...

The following measure calculates the total of "Sum" for all selected products.
Total - Selected Products :=
CALCULATE( SUM([Sum] ), ALLSELECTED(YourTableName[Name] ))

The following measure calculates the total of "Sum" does some sort of "ALLSELECTED EXCEPT Year" calculation (grand total for the current year):
Grand Total - Year :=
CALCULATE( SUM([Sum] ), ALLSELECTED(), VALUES(YourTableName[Year]))

For the given layout, both calculations will return the same results, but if you had another attribute in your table like "Country", they would give different results.

Take a look at both measures, to see which one is best suited.

 

[JavierGuillen]

If I understand the issue correctly, all you need is a measure that takes the current value (per [Name]) and divides its amount against the amount of the current Year. If this is the case, you can simply use the following as a measures:

SUM(Table1[Amount]) / CALCULATE( SUM(Table1[Amount]) , ALLEXCEPT(Table1, Table1[Year] ) )

The only issue would be the measure name, as you cannot dynamically title it (2011-Ratio or 2012-Ratio). It would have to be called just 'Ratio'