cancel
Showing results for
Did you mean:  Helper I

## A table of multiple values was supplied where a single value was expected. (when ALL() is applied)

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:

AG Total MGO (LS) = Calculate(
Sum( factAccountingTransactions[amount]);
factAccountingTransactions[account] >= 15100;
factAccountingTransactions[account] <= 15190;
FILTER( 'factAccountingTransactions'; ALL((factAccountingTransactions[dim_5])) <= MAX(factAccountingTransactions[dim_5])))

Intention with this formula is to SUM the amounts in [amount], for the given [account]s (between 15100-15190) for each [dim_5].
As the sum for MAX([dim_5] should be cumulative, I have added the FILTER as above.

Example of what the table looks like:
 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

I would like this example to return this sum table ():

 dim_5 amount GRL053 -4718,65 ICE006 22399,08 DPN006 5234,27 BCE182 41580 IRL069 -48018,1 IRL070 -176173,74

IRL069 and IRL070 is an example of the running sequence and also where IRL070 should return the cumulative value of SUM of IRL069+IRL070.

A few observations from my side which could be an issue?
- [dim_5] is text, but I see PowerBI is able to sort it by the number value, and I assume it could also find MAX?
I have also tried sorting by a numerical ID instead of the [dim_5], but with the same error.
- There may be multiple rows for each [dim_5] - ref above ICE006 and IRL069
- The visualization per [dim_5] works if not including ALL(), but then it obviously does not perform what I want (ie it is not cumulative).
1 ACCEPTED SOLUTION  Super User IV

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 a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

8 REPLIES 8  Helper I

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

Total Cumulative Amount  = Calculate(
Sum( 'Table'[amount]);
FILTER( 'Table'; ALL('Table'[ID]) <= MAX('Table'[ID])))

I now base the filtering on the ID instead to see if it is the Letter+number combinations which is making problems. But so far, no luck. Still get the same error message.  Super User IV

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 a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  Helper I

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;

Total Cumulative Amount =
VAR _max = MAX('Table'[ID])
RETURN
Calculate(
Sum( 'Table'[amount]);
FILTER( ALL('Table'); 'Table'[ID] <= _max))

This returns this table:

 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  Helper I

Had to specify column for ALL and not apply it for the full table.

So this formula solved it:

Total Cumulative Amount =
VAR _max = MAX('Table'[ID])
RETURN
Calculate(
Sum( 'Table'[amount]);
FILTER( ALL('Table'[ID]); 'Table'[ID] <= _max))

Thank you all for your input!  Microsoft

Hi @bbirkenes,

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Helper I

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.  Super User IV

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])))```

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!  Helper I

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) Announcements #### Welcome to the User Group Public Preview  