MDX Scope Total Calculation with condition


MDX Scope Total Calculation with condition



I recently encounter an issue with using scope to calculate grand total. Example I've create a measure :


CREATE MEMBER CURRENTCUBE.[Measures].[test Invoice 2 Amt]
AS IIF(ISEMPTY([Measures].[Amount]), NULL, [Measures].[SalesInvoiceLine2_Amt]),
FORMAT_STRING = "#,#.00",
VISIBLE = 1;



This measure to ensure amount2 (SalesInvoiceLine2_Amt) only appear when another measure (Amount) have value. But now the issue is my grand total for Amount2 doesn't total based on above condition, instead it sum up all amount2. I've try to use scope but seems doesnt work as well.


CREATE MEMBER CURRENTCUBE.[Measures].[test Invoice 2 Amt]
AS IIF(ISEMPTY([Measures].[Amount]), NULL, [Measures].[SalesInvoiceLine2_Amt]),
FORMAT_STRING = "#,#.00",
VISIBLE = 1;
SCOPE
(
[Measures].[test Invoice 2 Amt]
);
THIS = SUM(IIF(ISEMPTY([Measures].[Amount]), NULL, [Measures].[SalesInvoiceLine2_Amt])) ;
End Scope;

[Item] [Amount] [test Invoice 2 Amt] [SalesInvoiceLine2_Amt]
A 200
B 100 500 500
C 300
D 600 200 200
Total 700 11000 11000



The total for [Test Invoice 2 Amt] should be 500+ 200 = 700.
Thanks in advance.









By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

-

Popular posts from this blog

How to input without newline? (Python)

How to input without newline? (Python) I am trying to create a simple file editor in Python via the console (called PyCons). Here is my current program: def PyCons(f): file = open(f, "r") appe = open(f, "a") print("n=====nPyCons Editor, v1.2nPython 3.6.1n") global i i = 0 for line in file: i += 1 print(" {}| ".format(i) + line, end='') for k in range(10000000000000): print("n {}| ".format(i+1), end='') inp = input("") if inp == "@PyCons.save()": print("n=====") break else: i += 1 appe.write("n" + inp) I used the end method to make sure that the existing code in the file was printed properly in its line format, but the input function does not have an end attribute. So, when I go to enter code in the editor: end input end PyCons("magic.html") ... ===== PyCons Editor, v1.2 Python 3.6.1 1| <p>Hello!...
Read more

C++ thread error: no type named ‘type’ MINGW

C++ thread error: no type named ‘type’ MINGW I'm currently doing a project for my operational systems class and I have to make a program to find prime numbers to run in threads. So I did this: #include <iostream> #include <cmath> #include <thread> #define THREADNUMBER 100 using namespace std; int CONTADOR = 0; int CONTADORTHREADEXECUTADA = 0; int primeRange(int v1, int v2) { int a, limit; bool isprime; for (int i = v1; i <= v2; i++) { if (i % 2 != 0 && i % 3 != 0) { isprime = true; limit = i / 2; if(i == 1) isprime =false; limit = (int)sqrt(i); //General case for(a=2; a <= limit; a++){ if(i % i == 0 && i != 2){ isprime = false; break; } } if (isprime) { CONTADOR++; } } } CONTADORTHREADEXECUTADA++; return 1; } int main(int argc, char *argv[ ] ) { int number1 = atoi(argv[1]); int num...
Read more

Analog for TagView in flutter

Image
Analog for TagView in flutter I want to have UI like this: With native Android it can be done with libraries like https://github.com/whilu/AndroidTagView, how can it be done with flutter? 2 Answers 2 you can make the same UI by combining the Wrap and Chip widget as @wasyl montioned . but this is a full example about what you need Wrap Chip Notes : spacing deleteIcon avatar Chip Shape new Column( crossAxisAlignment: CrossAxisAlignment.stretch, children: <Widget>[ new Wrap( children: <Widget>[ Chip( label: new Text("Java"), onDeleted: () {}, labelPadding: EdgeInsets.all(2.0), deleteIcon: Icon(Icons.clear), ), Chip( label: new Text("C++"), onDeleted: () {}, labelPadding: EdgeInse...
Read more