Get in DAX an alternative to Calculated Member MDX

Question

I am trying to build a Financial model and is dealing with adding "Gross profit", "Operation Profit", "Net Profit", etcetera, to a genral Ledger dimension of a P&L.

The technique I used in MDX was to add calculated members with a syntax that looked +- like this:

CREATE MEMBER CURRENTCUBE.[Grouping].[All GLD].[Gross Profit]  AS 'SUM({[Grouping].[All GLD].firstchild : [Grouping].[All GLD].[Cost of Sales] })')

How do I get something like that or a nice alternative in DAX?

Answer 1

Go through the given link, it may help you for your query.

http://www.ssas-info.com/analysis-services-articles/50-mdx/2072-thoughts-on-how-powerpivot-and-ssas-could-work-together

Answer 2

Nanurahi

Thanks for directing me to an intresting blog, but NO, it didn't move me any nearer to a work around.

I do believe there are workarounds in DAX, and I didn't think the MDX solution was perfect too.

Still looking for assistance…

Answer 3

Hi there,

 

You could define these as calculated columns.  See these postings for details.

http://grow-n-shine.blogspot.ca/2012/01/microsoft-bi-defining-dax-calculations.html

http://www.bidn.com/blogs/DevinKnight/ssis/2086/powerpivot-ndash-commonly-used-dax-expressions

 

Another method using DAX

http://www.sqlbi.com/articles/converting-mdx-to-dax-first-steps/

 

A cheat sheet

http://powerpivot-info.com/post/439-dax-cheat-sheet

 

Haven’t seen a way to create new rows / members in the data.  Another posting related.

http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/f383c6c7-a4aa-4b62-a323-1a4b80758f07

 

cheers,

Andrew

 

"Urielil" wrote in message news:4f67b514-3743-4557-bb4c-715d9a790802...

I am trying to build a Financial model and is dealing with adding "Gross profit", "Operation Profit", "Net Profit", etcetera, to a genral Ledger dimension of a P&L.

The technique I used in MDX was to add calculated members with a syntax that looked +- like this:

CREATE MEMBER CURRENTCUBE.[Grouping].[All GLD].[Gross Profit]  AS 'SUM({[Grouping].[All GLD].firstchild : [Grouping].[All GLD].[Cost of Sales] })')

How do I get something like that or a nice alternative in DAX?

---

Andrew Sears, T4G Limited, http://www.performancepointing.com

Answer 4

Hi Androw

Thanks for the many link you suggested I look in to all of them, but:

Only one has information I think may be relevant and that is: Another method using DAX http://www.sqlbi.com/articles/converting-mdx-to-dax-first-steps/ .

But I am sorry to say there is probably something missing there, or I missed something in Power Pivot.  I can't understand how to enter what he shows  with the "ADDCOLUMNS" or "SUMMARIZE" functions in my Power Pivot model. Perhaps the example is only relevant for working inside the SQL Server 2012 and not the Excel Power Pivot?

Or what am I missing?

Still looking for assistance…

Answer 5

Check out this article about creating a cash flow statement in PowerPivot.

http://www.powerpivotpro.com/2011/12/cash-flow-statement-in-powerpivot/

If that doesn't solve your problem you may consider trying to add calculated members in Excel with a tool like OLAP PivotTable Extensions.

Answer 6

I am assuming that you know the list of values that need to be summed up. In this example, Gross Profit = Cost of Sales + ValA+ValB+....

1) So, you can create a new column in your powerpivot model with the formula

=if([All GLD]="Cost of Sales" or [All GLD]="ValA" or "ValB" or .... , "Gross Profit", Blank())

2) Once that is done, you can refer to this new column and select the Gross Profit value to get the desired results

You can go further and then make a measure with a if condition which gives Blank if it is not Gross Profit, and hence you can refer the measure directly also.

This is just a hint for you to go further, feel free to ask if you are not clear :)

---

Cheers,
Jason
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Answer 7

Hi Ruve1k

The exact link you send returns an Error, still looking in the site I think I found the article about Cash flow, it was interesting, but did not deal with the much more fundamental need in financial reporting that I am looking for.

Yes to write it on MDX to the 2010 Pivot table is an option that can give the result to that one pivot table, but that is a bad option. I am looking for a solution inside the cube (sorry inside the cube substitution…)

Still looking for assistance…

 

Answer 8

Hi Jones

Perhaps I miss something from what you suggest, or otherwise what you suggest gives me only a last option, if I do not find what I am looking for.

I am interested to have more than one balance line that overleaps with other balance lines (the "Operating profit" includes all lined form the "Grass profit" and more. Then I want to have them with the top level ("Sale", "Cost of sale" etcetera) in the same level and without un needed subtotals and headings . Her is an example form a pivot table connected to cube (MDX)

Still looking for assistance…

Answer 9

try this friendo :]]

CREATE MEMBER CURRENTCUBE.[Grouping].[All GLD].[Gross Profit]  AS 'SUM({[Grouping].[All GLD].firstchild : [Grouping].[All GLD].[Cost of Sales] })')

So its going to be:::

you need to break down the calc into 2 stages

first create a measure which sums cost of sales -

So: SumCostOfSales:=Calculate(Sum(All GLD[Cost Of Sales]) for the 'hidden' variable

then create a second measure as follows:

YourMeasureName:=Calculate(SUMX(All GLD, ALL GLD[SumCostOfSales]),ALL(ALL GLD[firstchild]))

When you say ALL(All GLD[firstchild] this is essentially your filtering -

Using sumx basically lets you use a variable in your formula - and the variable in this is the SumCostofSales -

it then using the all functions tells it to sum all sales - by individual rows and split by cost of sales -

Try adding a slicer in your report to break down the information even further for the end users ease

hope this helps -

PS as a free tip this might get slow if using millions of rows so my tip here is to run the profiler on the database and put the trc file through tne engine tuning adviser .. make sure you have a damn good click through every section of the report whenyou refresh the report and apply the stats it reccomends - then in sql window run exec sp_updatestats and retry - the performance increase is huuuge on this kind of query doing so ..

gll!

Answer 10

thanks James but....

In my MDX it's a Dimension, it runs an all measures simultaneously, (actual, Budget, last yea, YTD, % of income, excreta…) you suggest I first build a measure, that looks too complicated.

the Excel example I should have given should have looked like this: