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
- Calculated Column Constraints with Data 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

cpiercey

Frequent Visitor

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

08-07-2020
03:30 PM

Hello, I am experiencing an unexpected issue with a calculated column and I am wondering if it has to do with the way I created the table it is referencing. I have the following tables:

1. Unfiltered Forecast - Created using SUMMARIZE with master data import tables

2. Unfiltered Demand - Created by grouping by inventory ID # and contains a calculated column which references the Unfiltered Forecast table

3. Filtered Forecast - Created using a UNION on two tables

4. Filtered Demand - Created by grouping by inventory ID # and contains a calculated column which references the Filtered Forecast table

Here is the calculated column used in #2:

2018 Q1 Demand = CALCULATE(sum('Unfiltered Forecast'[Total Quantity]), 'Unfiltered Forecast'[Z Outlier] = "Normalized", 'Unfiltered Forecast'[Date - Year] = 2018, 'Unfiltered Forecast'[Date - Quarter] = 1)

This column is able to perform the calculation and find the sum of Total Quantity for the given parameters to the CALCULATE() function for each specific row. For table #4, I use basically the exact same column except that it has one less filter and references table #3:

2018 Q1 Demand = CALCULATE(sum('Filtered Forecast'[Total Quantity]), 'Filtered Forecast'[Date - Year] = 2018, 'Filtered Forecast'[Date - Quarter] = 1)

However, this column is unable to calculate row-specific values. It calculates one (very large) value and applies it to all of the rows. Can you please help me figure out what is causing this loss of row-specific calculation? Thanks in advance for taking a look.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

cpiercey

Frequent Visitor

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

08-13-2020
09:17 AM

Thanks for the response, @AllisonKennedy. I ended up adding additional context to the original formula which allowed the calculation to execute at the row-level. The end result needed to be a summation of quantities for each item in a given time period based on a transaction table with many transactions for that item. I have it working now, I also took your advice on the DimDate table, thanks for the tip.

4 REPLIES 4

Highlighted
##

AllisonKennedy

Super User IV

Re: Calculated Column Constraints with Data Table

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

08-08-2020
03:00 AM

@cpiercey Can I ask what result you are expecting?

You are doing a SUM inside a calculated column, which is generally not advised. You have nested it inside a CALCULATE which does change the context and makes it provide ok results, but I still don't recommend this approach.

What do you want as an end result?

Try using calculated MEASURES for some of your formulas instead, and put them in a matrix visual with Year and Quarter.

I also recommend using a DimDate table rather than the Forecast table dates: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

You are doing a SUM inside a calculated column, which is generally not advised. You have nested it inside a CALCULATE which does change the context and makes it provide ok results, but I still don't recommend this approach.

What do you want as an end result?

Try using calculated MEASURES for some of your formulas instead, and put them in a matrix visual with Year and Quarter.

I also recommend using a DimDate table rather than the Forecast table dates: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

______________

Has this post solved your problem? Please **mark it as a solution** so that others can find it quickly and to let the community know your problem has been solved.

If you found this post helpful, please **give Kudos**.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

Highlighted
##

V-pazhen-msft

Community Support

Re: Calculated Column Constraints with Data Table

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

08-09-2020
11:40 PM

@cpiercey

I wonder why do you create the column in table 4 while it calculate with the column in table 3. Why don't you just create the column in Table 3.

Basically the expression column of Calculate(xxxx) must from the current table, you can filter with others tables if there is a relationship, something like:

`2018 Q1 Demand = CALCULATE(sum('Filtered Demand'[Total Quantity]), 'Filtered Forecast'[Date - Year] = 2018, 'Filtered Forecast'[Date - Quarter] = 1)`

Paul Zheng _ Community Support Team

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

Highlighted
Thanks for the response, @AllisonKennedy. I ended up adding additional context to the original formula which allowed the calculation to execute at the row-level. The end result needed to be a summation of quantities for each item in a given time period based on a transaction table with many transactions for that item. I have it working now, I also took your advice on the DimDate table, thanks for the tip.

cpiercey

Frequent Visitor

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

08-13-2020
09:17 AM

Highlighted
##

AllisonKennedy

Super User IV

Re: Calculated Column Constraints with Data Table

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

08-13-2020
04:08 PM

@cpiercey

Glad you got it working!

Please can you mark this post as solved so others know it is resolved and can find the solution easily?

Thanks!

Glad you got it working!

Please can you mark this post as solved so others know it is resolved and can find the solution easily?

Thanks!

______________

Has this post solved your problem? Please **mark it as a solution** so that others can find it quickly and to let the community know your problem has been solved.

If you found this post helpful, please **give Kudos**.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

Top Solution Authors

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

412 | |

313 | |

81 | |

57 | |

46 |

Top Kudoed Authors

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

564 | |

502 | |

162 | |

141 | |

128 |