Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Sum with filter and cross table

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

John117

New Member

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-01-2020
02:02 PM

I have 2 tables, one with the date, customer name, and the gross sales and another table that is a selection of the most relevant customer by date, like this:

Table 1

Date | Customer | Gross Sales |

01/06/2020 | X | 300 |

01/06/2020 | Y | 200 |

01/07/2020 | Y | 100 |

01/07/2020 | Z | 150 |

Table 2

Date | Customer |

01/06/2020 | X |

01/07/2020 | Y |

01/07/2020 | X |

I need to calculate the total sales of the most relevant customers, but it's not working. I'm using this formula:

Total gross = calculate(sum(Table1[Gross Sales]), filter(Table1, Table1[customer]=values(Table2[customer])))

The error message says 'MdxScript(Model) (4,102) Calculation error in measure: A table of multiple values was supplied where a single value was expected'

Could someone help me? I'm beginner in Power BI

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

Icey

Community Support

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-03-2020
01:37 AM

Hi @John117 ,

VALUES() returns a single column table or a table of the same columns. In your scenario, VALUES(Table2[customer]) returns a table with a single column. Your problem is that a column cannot equal a table. You can use IN instead of “=”.

IN Creates a logical OR condition between each row being compared to a table. Note: the table constructor syntax uses curly braces. 'Product'[Color] IN { "Red", "Blue", "Black" }

The formula can be modified as follows:

```
Total gross 2 =
CALCULATE (
SUM ( Table1[Gross Sales] ),
FILTER ( Table1, Table1[customer] IN VALUES ( Table2[customer] ) )
)
```

The result is shown in the following figure:

The above formula is limited to filtering customer names. If you also want to filter by date and customer name at the same time. You can write a calculated columns or a measure.

You can write your calculated column like so:

```
Total gross =
CALCULATE (
SUM ( Table1[Gross Sales] ),
FILTER (
Table1,
Table1[customer] = 'Table2'[customer]
&& Table1[Date] = Table2[Date]
)
)
```

The result is shown in the following figure:

Or you can write your measure like so:

```
Total gross3 =
VAR table3 =
ADDCOLUMNS (
Table2,
"Total gross4", CALCULATE (
SUM ( Table1[Gross Sales] ),
FILTER (
Table1,
Table1[Customer] = Table2[customer]
&& Table1[Date] = Table2[Date]
)
)
)
RETURN
SUMX ( table3, [Total gross4] )
```

The result is shown in the following figure:

You can check more details from here.

Best Regards,

Icey

If this post **helps**, then please consider * Accept it as the solution* to help the other members find it more quickly.

3 REPLIES 3

Highlighted
##

lbendlin

Community Champion

Re: Sum with filter and cross table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-01-2020
02:05 PM

Yes, VALUES() returns {X,Y} from Table 2. Don't you also want to filter by date? And correct your sample data in Table 2?

Highlighted

Icey

Community Support

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-03-2020
01:37 AM

Hi @John117 ,

VALUES() returns a single column table or a table of the same columns. In your scenario, VALUES(Table2[customer]) returns a table with a single column. Your problem is that a column cannot equal a table. You can use IN instead of “=”.

IN Creates a logical OR condition between each row being compared to a table. Note: the table constructor syntax uses curly braces. 'Product'[Color] IN { "Red", "Blue", "Black" }

The formula can be modified as follows:

```
Total gross 2 =
CALCULATE (
SUM ( Table1[Gross Sales] ),
FILTER ( Table1, Table1[customer] IN VALUES ( Table2[customer] ) )
)
```

The result is shown in the following figure:

The above formula is limited to filtering customer names. If you also want to filter by date and customer name at the same time. You can write a calculated columns or a measure.

You can write your calculated column like so:

```
Total gross =
CALCULATE (
SUM ( Table1[Gross Sales] ),
FILTER (
Table1,
Table1[customer] = 'Table2'[customer]
&& Table1[Date] = Table2[Date]
)
)
```

The result is shown in the following figure:

Or you can write your measure like so:

```
Total gross3 =
VAR table3 =
ADDCOLUMNS (
Table2,
"Total gross4", CALCULATE (
SUM ( Table1[Gross Sales] ),
FILTER (
Table1,
Table1[Customer] = Table2[customer]
&& Table1[Date] = Table2[Date]
)
)
)
RETURN
SUMX ( table3, [Total gross4] )
```

The result is shown in the following figure:

You can check more details from here.

Best Regards,

Icey

If this post **helps**, then please consider * Accept it as the solution* to help the other members find it more quickly.

Highlighted
##

John117

New Member

Re: Sum with filter and cross table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-03-2020
04:48 AM

Now it's working. Thank you so muck, @Icey !

Announcements

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Visit our Community Blog for articles, guides, and information created by fellow community members.

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors

User | Count |
---|---|

364 | |

129 | |

92 | |

91 | |

91 |

Top Kudoed Authors

User | Count |
---|---|

475 | |

180 | |

177 | |

137 | |

120 |