Microsoft SQL Server
--
Questions
--
Followers
Top Experts
SSAS Cube
I have SSAS Cube and I am trying to use the calculation to calculate a Gross profit,
I have two cases (Internal & External Gross Profit) I used both IIf and case statment and it didnt work any ideas?
Code I used :
case when [Dim Invoices].[Internal External].currentmember Is [Dim Invoices].[Internal External].&[Internal] Then [Measures].[Invoice Qty] * 0.0075 Else ([Measures].[Total Revenue] - [Measures].[COGS] + [Measures].[Supplier Discount] +[Measures].[Total BLND Credit] + [Measures].[Supplier Rebates] + [Measures].[TVA] - [Measures].[Marathon Promotional Fund Fee]) End
--------------------------
IIF([Dim Invoices].[Internal External].currentmember Is [Dim Invoices].[Internal External].&[Internal],[Mea
I have two cases (Internal & External Gross Profit) I used both IIf and case statment and it didnt work any ideas?
Code I used :
case when [Dim Invoices].[Internal External].currentmember Is [Dim Invoices].[Internal External].&[Internal] Then [Measures].[Invoice Qty] * 0.0075 Else ([Measures].[Total Revenue] - [Measures].[COGS] + [Measures].[Supplier Discount] +[Measures].[Total BLND Credit] + [Measures].[Supplier Rebates] + [Measures].[TVA] - [Measures].[Marathon Promotional Fund Fee]) End
--------------------------
IIF([Dim Invoices].[Internal External].currentmember Is [Dim Invoices].[Internal External].&[Internal],[Mea
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Hi,
Try the following:
CREATE MEMBER CURRENTCUBE.[Measures].[Gr
AS NULL;
SCOPE ([Measures].[Gross Profit]);
SCOPE ([Dim Invoices].[Internal External].&[Internal]);
THIS = [Measures].[Invoice Qty] * 0.0075;
END SCOPE;
SCOPE ([Dim Invoices].[Internal External].&[External]);
THIS = [Measures].[Total Revenue] - [Measures].[COGS] + [Measures].[Supplier Discount] +[Measures].[Total BLND Credit] + [Measures].[Supplier Rebates] + [Measures].[TVA] - [Measures].[Marathon Promotional Fund Fee];
END SCOPE;
END SCOPE;
I'm assuming that there is an 'External' member in your internal extrnal dim here.
Also, you say that your calculation 'didn't work'. Could you provide more details e.g. did it give an error, include values it shouldn't, exclude values it shouldn't etc.
Thanks
Tim
Try the following:
CREATE MEMBER CURRENTCUBE.[Measures].[Gr
AS NULL;
SCOPE ([Measures].[Gross Profit]);
SCOPE ([Dim Invoices].[Internal External].&[Internal]);
THIS = [Measures].[Invoice Qty] * 0.0075;
END SCOPE;
SCOPE ([Dim Invoices].[Internal External].&[External]);
THIS = [Measures].[Total Revenue] - [Measures].[COGS] + [Measures].[Supplier Discount] +[Measures].[Total BLND Credit] + [Measures].[Supplier Rebates] + [Measures].[TVA] - [Measures].[Marathon Promotional Fund Fee];
END SCOPE;
END SCOPE;
I'm assuming that there is an 'External' member in your internal extrnal dim here.
Also, you say that your calculation 'didn't work'. Could you provide more details e.g. did it give an error, include values it shouldn't, exclude values it shouldn't etc.
Thanks
Tim
I have External and I have Null too!! The error I have #Value.
and where i can try this i am using the calculation tab on the Cube?
Thanks
and where i can try this i am using the calculation tab on the Cube?
Thanks
Hi,
Yes - you declare this in the calculation tab.
What's the member key value for you 'null' member? if it is blank then you should be able to use:
SCOPE ([Dim Invoices].[Internal External].&[]);
but just substitute whatever the member key value is.
Tim
p.s. if you hover over the #value error you might get more info on the nature of the problem
Yes - you declare this in the calculation tab.
What's the member key value for you 'null' member? if it is blank then you should be able to use:
SCOPE ([Dim Invoices].[Internal External].&[]);
but just substitute whatever the member key value is.
Tim
p.s. if you hover over the #value error you might get more info on the nature of the problem
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
added SSAS zone
aikimark -- zone advisor
aikimark -- zone advisor
how i can do that?
Sorry - you said you were using the calculation tab in the cube, which is what you want. Paste the code I submitted into there (select 'script view' from the menu first) and save the changes to the cube.
You should then be able to reference the Gross Profit measure in the cube browser.
Tim
You should then be able to reference the Gross Profit measure in the cube browser.
Tim
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
i TRIED TO CREATE IT AND I GOT THIS ERROR
Error 37 MdxScript(AOC_Cube) (21, 1) Parser: The syntax for ';' is incorrect. 0 0
Error 37 MdxScript(AOC_Cube) (21, 1) Parser: The syntax for ';' is incorrect. 0 0
Looks like there's a '.' in the line CREATE MEMBER CURRENTCUBE.[Measures].[Gr
between cube and [measures]
between cube and [measures]
it didnt work, gave me empty column
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Did you drop Dim Invoices on to grid? Scope will only work if the dimension referenced exists in the query.
Tim
Tim
no tim, I dont want to show it on the query
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
it didnt work.
I think this code is very close to the solution but still i have problem with the Total .
I sent u attached pic, did you c the total for the gross profit? its not correct. for example
the Ethanol product, we have External Gallon 231599 and internal Gallon 678871 Total Gallon is 910470 if you c the gross profit
it should be
External
$39,723.18
Internal
$5,091.53
Totalshould be $44,814.71 NOT $176,582.12
I think this code is very close to the solution but still i have problem with the Total .
I sent u attached pic, did you c the total for the gross profit? its not correct. for example
the Ethanol product, we have External Gallon 231599 and internal Gallon 678871 Total Gallon is 910470 if you c the gross profit
it should be
External
$39,723.18
Internal
$5,091.53
Totalshould be $44,814.71 NOT $176,582.12
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Sorry, I can't relate the image you've posted to the calculation.
In your grid I would have expected to see a column for 'Gross Profit Internal' and a column for Gross Profit External. And I wouldn't have expected the Internal / External dimension as you said you didn't want to include that in the query.
What are 'Condition1' and 'Condition2'?
In your grid I would have expected to see a column for 'Gross Profit Internal' and a column for Gross Profit External. And I wouldn't have expected the Internal / External dimension as you said you didn't want to include that in the query.
What are 'Condition1' and 'Condition2'?
Condition1:
[Measures].[Invoice Qty] * 0.0075
Condition2:
[Measures].[Total Revenue] - [Measures].[COGS] + [Measures].[Supplier Discount] +[Measures].[Total BLND Credit] + [Measures].[Supplier Rebates] + [Measures].[TVA] - [Measures].[Marathon Promotional Fund Fee]
Total Gross Profit:
IIF([Dim Invoices].[Internal External].currentmember Is [Dim Invoices].[Internal External].&[Internal],[Mea
+ [Measures].[Supplier Discount] +[Measures].[Total BLND Credit] + [Measures].[Supplier Rebates] + [Measures].[TVA] - [Measures].[Marathon Promotional Fund Fee]))
I add the internal and the external to make it clear, but in the production i will not show it i will show the total only but as you c the total is not right.
[Measures].[Invoice Qty] * 0.0075
Condition2:
[Measures].[Total Revenue] - [Measures].[COGS] + [Measures].[Supplier Discount] +[Measures].[Total BLND Credit] + [Measures].[Supplier Rebates] + [Measures].[TVA] - [Measures].[Marathon Promotional Fund Fee]
Total Gross Profit:
IIF([Dim Invoices].[Internal External].currentmember Is [Dim Invoices].[Internal External].&[Internal],[Mea
+ [Measures].[Supplier Discount] +[Measures].[Total BLND Credit] + [Measures].[Supplier Rebates] + [Measures].[TVA] - [Measures].[Marathon Promotional Fund Fee]))
I add the internal and the external to make it clear, but in the production i will not show it i will show the total only but as you c the total is not right.
Well you're not using the code I posted for [Measures].[Gross Profit Internal] and [Measures].[Gross Profit External] - why don't you try this?
What you're doing will not work because your calculations are based on a currentmember value for the internal / External dimension. There is no value for this in the overall total line - it will sum across all dimension members.
Tim
What you're doing will not work because your calculations are based on a currentmember value for the internal / External dimension. There is no value for this in the overall total line - it will sum across all dimension members.
Tim
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
I tried what that but it didnt work,
so how i can make it work for the total level?
what i am trying to do, i want to calculate the gross profit as we do in the SQL statment
case when [external/interl] = "Internal
Then .......
Else ..................
End as a 'Gross Profit'
can i do that?
so how i can make it work for the total level?
what i am trying to do, i want to calculate the gross profit as we do in the SQL statment
case when [external/interl] = "Internal
Then .......
Else ..................
End as a 'Gross Profit'
can i do that?
Why didn't the code I posted work - what was the result?
You won't be able to use any calculation based on checking a currentmember in a query that doesn't include the dimension breakdown. So no, you can't do what you're trying to do. The approach of calculating specific values based on tuples that explicitly filter the measure by the dimension values that you can then add together to form a total gross profit should work, though.
Tim
You won't be able to use any calculation based on checking a currentmember in a query that doesn't include the dimension breakdown. So no, you can't do what you're trying to do. The approach of calculating specific values based on tuples that explicitly filter the measure by the dimension values that you can then add together to form a total gross profit should work, though.
Tim
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Tim I change a little on ur last post and it work perfect thank alot
Calendar Year Month 2011-06
Status (Multiple Items)
Profit Center Name (Multiple Items)
State Code NM
Row Labels Invoice Qty Gross Profit External Gross Profit Internal Gross Profit Null Total Gross Profit
NEW MEXICO 925,788 $39,882.02 $5,194.28 $45,076.30
ETHANOL 910,470 $39,723.18 $5,091.53 $44,814.71
FLEXE85 6,377 $47.83 $47.83
ULS #2 7,323 $54.92 $54.92
UNL 86 1,618 $158.84 $158.84
Grand Total 925,788 $39,882.02 $5,194.28 $45,076.30
Calendar Year Month 2011-06
Status (Multiple Items)
Profit Center Name (Multiple Items)
State Code NM
Row Labels Invoice Qty Gross Profit External Gross Profit Internal Gross Profit Null Total Gross Profit
NEW MEXICO 925,788 $39,882.02 $5,194.28 $45,076.30
ETHANOL 910,470 $39,723.18 $5,091.53 $44,814.71
FLEXE85 6,377 $47.83 $47.83
ULS #2 7,323 $54.92 $54.92
UNL 86 1,618 $158.84 $158.84
Grand Total 925,788 $39,882.02 $5,194.28 $45,076.30
Microsoft SQL Server
--
Questions
--
Followers
Top Experts
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.