Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
In short:
I want to calculate the cumulative sum of a column based on a filter on a reference number.
I receive the error: MdxScript(Model) (74, 43) Calculation error in measure 'Measures 1'[Test 2 AG Total MGO (LS)]: A table of multiple values was supplied where a single value was expected.
My calculation is:
account | amount | dim_5 |
15134 | -4718,65 | GRL053 |
15134 | -5234,27 | ICE006 |
15134 | 5234,27 | DPN006 |
15134 | 41580 | BCE182 |
15134 | -128155,64 | IRL070 |
15134 | -127615,1 | IRL069 |
15133 | 79597 | IRL069 |
15133 | 27633,35 | ICE006 |
dim_5 | amount |
GRL053 | -4718,65 |
ICE006 | 22399,08 |
DPN006 | 5234,27 |
BCE182 | 41580 |
IRL069 | -48018,1 |
IRL070 | -176173,74 |
Solved! Go to Solution.
The main issue is that you have your ALL in the wrong spot. That's what's generating your error. But, I would do it this way:
Total Cumulative Amount = VAR __max = MAX([ID]) RETURN Calculate( Sum( 'Table22'[amount]), FILTER(ALL('Table22'), 'Table22'[ID] <= __max))
See Page 13, Table 22
I have now simplified the example table a bit more to try to find the error.
This is now the current table, and I still cannot present a table visual.
account | amount | dim_5 | Voy_ID | dim_2 | ID |
15134 | -4718,65 | GRL053 | 24233 | GRL | 1 |
15133 | 27633,35 | ICE006 | 79265 | ICE | 2 |
15134 | 5234,27 | DPN006 | 89988 | DPN | 3 |
15134 | 41580 | BCE182 | 35839 | BCE | 4 |
15133 | 79597 | IRL069 | 31933 | IRL | 5 |
15134 | -128155,64 | IRL070 | 53304 | IRL | 6 |
My formula is now simplified to
The main issue is that you have your ALL in the wrong spot. That's what's generating your error. But, I would do it this way:
Total Cumulative Amount = VAR __max = MAX([ID]) RETURN Calculate( Sum( 'Table22'[amount]), FILTER(ALL('Table22'), 'Table22'[ID] <= __max))
See Page 13, Table 22
Thank you @Greg_Deckler,
but it doesn't work.
The total sum of the column [amount] in the example is 21.170,33.
I'm now using the formula as suggested;
dim5 | Total Cumulative Amount |
BCE182 | 21170,33 |
DPN006 | 21170,33 |
GRL053 | 21170,33 |
ICE006 | 21170,33 |
IRL069 | 21170,33 |
IRL070 | 21170,33 |
Total | 21170,33 |
Had to specify column for ALL and not apply it for the full table.
So this formula solved it:
Thank you all for your input!
Hi @Anonymous,
IRL069 and IRL070 is an example of the running sequence and also where IRL070 should return the cumulative value of SUM of IRL069+IRL070.
How to determine "running sequence"? In above example, as IRL069 and IRL070 share the same beginning alphabet "IRL" and 69, 70 are continual numbers, we should calculate cumulative total, right?
Best regards,
Yuliana Gu
Hi @v-yulgu-msft,
The IRL is identifying a vessel, while the number identifies a voyage, and it will follow the sequence IRL069 - IRL070 - IRL071 etc.
And yes - we are looking for cumulative total.
I believe you want:
AG Total MGO (LS) = Calculate( Sum( factAccountingTransactions[amount]); factAccountingTransactions[account] >= 15100; factAccountingTransactions[account] <= 15190; FILTER( ALL('factAccountingTransactions'); factAccountingTransactions[dim_5] <= MAX(factAccountingTransactions[dim_5])))
Thanks @Greg_Deckler,
but unfortunately that does not solve it. It actually gives quite unexpected results, which I cannot really make sense of.
I manage to display a visual, but it will not be filtered by dim_5. It will be a sum of all rows on each dim_5 (same value all over).
(Edit post after a bit of testing)
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |