May 6, 2015 at 5:17 am
Hi, hopefully I can make sense with this!
I have a pivot table in Excel that is connected to a Tabular SSAS model. I have a fact table with a degenerate dimension called 'Type' and 'AccountNo'. I need to put Type in the 'Columns' section in the pivot, and AccountNo in the Rows section. A 'Sum of Total' measure goes into the values.
The Type dimension has three values, 'A', 'B' and 'C'. What I need to do is a calculation that would do (A+B) - C, and have this as a column at the end (where the grand total usually is)
Looking like this;
Type
RowsABCTotal
A1231010515
A4562020535
A7891020525
Where would I do this? In DAX? I thought of doing a calculated column but the option is greyed out in Excel.
Any help is much appreciated! Thanks 🙂
May 6, 2015 at 7:21 am
I am assuming that you have 3 different columns A, B and C in Type Dimension.
I would prefer to do it at Tabular model using DAX formula.
I create new column Total with formula A+B-C.
You also can do a grand total for each column at tabular. and it is flexible in Pivot. You can use that in Value section and it calculates based on the dimensions you pull at Row and Column sections.
after that if you pull these A, B, C and Total columns to in Pivot as values.
Grand Total automatically comes in Pivot. You don't need to separately calculate it.
Hope this helps 🙂
Thank You
May 6, 2015 at 7:21 am
Unless you created the Tabular Model using Excel (Powerpivot) then you have to manage custom calculations at server level. You have a number of options which are detailed here: https://msdn.microsoft.com/en-us/library/hh212939.aspx
May 6, 2015 at 7:34 am
Sorry VRT, let me explain a bit more..
The fact table has a dimension in it, which is deep and in one column. This column has 3 possible values. When I drag this column in the columns section in a pivot, the 3 values appear horizontally.
So.. the fact table is like this;
Acc Type Total
1 A 10
1 B 20
1 C 30
2 A 40
2 B 50
2 C 60
When I put a pivot on top of this, I'll want Acc in the rows, Type in the columns, and SumOfTotal in the values. This is when A B C will show horizontally.
I have managed to do a long winded workaround. I've created this measure;
TypesDifference:=(CALCULATE(SUM([Total]),Table[Type]="A") + CALCULATE(SUM([Total]),Table[Type]="B")) - CALCULATE(SUM([Total]),Table[Type]="C")
This gives me a measure that give me what I want. Only issue is, when I put it in a pivot, it duplicates the Type column because I have two values. It works, although a bit messy. Any other alternatives? Ideally I want the SumOfTotal to be split by the Type in the columns section, and the TypesDifference measure I created to be shown as only a grand total.
May 7, 2015 at 3:18 am
Anyone? 🙂
May 7, 2015 at 7:22 am
I dont think there is an option to customize the Pivot table. you can't control the pivot table columns and rows. It automatically takes the second column and repeats for A,B and C.
and also we cant control the Grand Total formula at Pivot table.
I think the only way to do that is...
you have to convert the the rows (each Type) to columns in Tabular. I don't know how many types you have. If you have manageable number than take this approach.
Take Sum for each Type and then later calculate TypeDiff.
In Pivot table, bring the each Type column to values, and then TypeDiff also to values.
I also work most on PowerPivot and Tabular. This is the only way I thought.
you can control Pivot (Matrix) in SSRS reports but not in Excel as far as I know.
Thank You
May 7, 2015 at 8:16 am
I've taken a slightly different approach.
I created a calculated column that reversed the sign for the total column if the type ='C'. I then created a measure for this new column and voila!
If the user ever wanted the original totals then they can still use the original total measure. Feels wrong, but works!
May 7, 2015 at 8:23 am
mm7861 (5/7/2015)
I've taken a slightly different approach.I created a calculated column that reversed the sign for the total column if the type ='C'. I then created a measure for this new column and voila!
If the user ever wanted the original totals then they can still use the original total measure. Feels wrong, but works!
This is good idea. If it okay to show the Type C balance in negatives then this is good approach only. 🙂
Thank You
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply