cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bbirkenes
Helper I
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: 
accountamountdim_5
15134-4718,65GRL053
15134-5234,27ICE006
151345234,27DPN006
1513441580BCE182
15134-128155,64IRL070
15134-127615,1IRL069
1513379597IRL069
1513327633,35ICE006
 
 
I would like this example to return this sum table (): 
 
dim_5amount
GRL053-4718,65
ICE00622399,08
DPN0065234,27
BCE18241580
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

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
bbirkenes
Helper I
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. 

 

accountamountdim_5Voy_IDdim_2ID
15134-4718,65GRL05324233GRL1
1513327633,35ICE00679265ICE2
151345234,27DPN00689988DPN3
1513441580BCE18235839BCE4
1513379597IRL06931933IRL5
15134-128155,64IRL07053304IRL6

 

 

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. 

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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: 
 
dim5Total Cumulative Amount
BCE18221170,33
DPN00621170,33
GRL05321170,33
ICE00621170,33
IRL06921170,33
IRL07021170,33
Total21170,33

 

 

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! 

v-yulgu-msft
Microsoft
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.

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. 

Greg_Deckler
Super User IV
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])))

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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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)

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors