Display a fixed value from one table to a table that has many related records without duplicating.

[ChuckChuck]

Hello, 

I'm new to Power BI and more importantly to DAX. I have what is probably something of a simply issue, but no matter how much time I spend trying to get a working solution, I keep coming up short. 

I have a table that stores a list of customers underneath a workstream. One customer cannot belong to more than one workstream. Each customer has a defined goal that will not change. A second table stores customers and their actual monthly values. Because this table stores monthly actuals, a single customer will be listed many times. In my visiual, the goal is being added each time for each monthly instace of the same customer. How can I avoid this behaviour and report the overall goal at the customer level, which rolls up to the workstream level as a subtotal, without subtotalling it for each related monthly record?

[Greg_Deckler]

Having a difficult time picturing this in my head. Can you supply sample or mocked up data with what you are trying to achieve? There is almost certainly an answer but I can't piece it together from what you have described.

[ChuckChuck]

Smourpre,

Thank you for taking the time. Here's an example dataset from Excel. On the left is one table in Power BI and on the right is the second (Customer Goal and Customer Actuals, respectively). I've merged the two tables together in Power BI's query editor. What happens in doing so is that the Goal becomes repeated for each line item. There are also some lines from the Customer Actuals table that I wouldn't expect a Goal to be measured against, because the line item is out of scope. In Excel I'd just use the sumifs and countifs functions to only to arrive at the proper line item amount. That way when I subtotal the Customer Actuals table, the customer and the workstream will subtotal back to what I see in the Customer Goal table. The last column in the example below shows the end product of applying the sumifs/countifs functions. I'm sure this exists for the DAX language, but I just can't seem to get a working formula. 

[Greg_Deckler]

Lots going on here and I think I've lost track of the question being asked. Couple things. why merge the tables versus just create a relationship between them? Second, SUMIF equivalent in DAX is handled by CALCULATE:

https://msdn.microsoft.com/en-us/library/ee634825.aspx

[ChuckChuck]

My first attempt was to simply join the customer from the Goals table to the customer from the Actuals table, but what's happening when I drop the goal value into the table visual in Power BI is that since the join is a one to many, the goal is being subtotaled for all customers and the resulting grand total is being repeated for each line in the table visual. What I'm trying to do with the visual is have a table that users can drill down on, comparing how close the workstream or customers under it are between the goal and the actuals. 

Since the visual is repeating the goal due to the one to many relationship, I thought that maybe joining the two tables into one query and then writing a formula to divide the goal by the number of lines would be the best bet. That way when I subtotal the individual lines in the Power BI table visual, it adds up correctly. 

I've tried so many formulas trying to get it to work, but I'm coming up short. I'm still very green with DAX, so I'm probably missing some bit of logic...

Thank you for the link to the calculate resource. I'll read through it and see if that's the solution I'm after. 

[Greg_Deckler]

So, your first table, that seems pretty straight-forward. What does the raw (before your join) second table look like? Can you post that?