Forum Discussion
Using specific data within a column in another column in the same table
I have the following data in which a higher level category (Fruit) is mixed in with its subcategories (Apples, Oranges, and Bananas). Note that the Quantity for Fruit for each Period is the sum of Quantity for Apples, Oranges, and Bananas for that same Period.
| Category | Period | Quantity |
| Fruit | 201601 | 20 |
| Fruit | 201602 | 19 |
| Fruit | 201603 | 20 |
| Fruit | 201604 | 18 |
| Apples | 201601 | 3 |
| Apples | 201602 | 2 |
| Apples | 201603 | 3 |
| Apples | 201604 | 4 |
| Oranges | 201601 | 10 |
| Oranges | 201602 | 11 |
| Oranges | 201603 | 10 |
| Oranges | 201604 | 9 |
| Bananas | 201601 | 7 |
| Bananas | 201602 | 6 |
| Bananas | 201603 | 7 |
| Bananas | 201604 | 5 |
I want to add a new calculated column FruitTotal that repeats the Quantity values for Fruit for each Period across all of the subcategories Apples, Organges, and Bananas, so that the result looks like this (note how the FruitTotal for the Category Fruit repeats for each successive subcategory of Apples, Oranges, and Bananas, corresponding to each Period):
| Category | Period | Quantity | FruitTotal |
| Fruit | 201601 | 20 | 20 |
| Fruit | 201602 | 19 | 19 |
| Fruit | 201603 | 20 | 20 |
| Fruit | 201604 | 18 | 18 |
| Apples | 201601 | 3 | 20 |
| Apples | 201602 | 2 | 19 |
| Apples | 201603 | 3 | 20 |
| Apples | 201604 | 4 | 18 |
| Oranges | 201601 | 10 | 20 |
| Oranges | 201602 | 11 | 19 |
| Oranges | 201603 | 10 | 20 |
| Oranges | 201604 | 9 | 18 |
| Bananas | 201601 | 7 | 20 |
| Bananas | 201602 | 6 | 19 |
| Bananas | 201603 | 7 | 20 |
| Bananas | 201604 | 5 | 18 |
I can't hard code the FruitTotal column contents because I need it to be dynamic, i.e., when the Period 201605 is added with new quantities for Fruit, Apples, Oranges, and Bananas, I would want the FruitTotal column to update accordingly. (Why do I want this? Ultimately I want to calculate the percentage of each Apples, Oranges, and Bananas against the Fruit category for each Period, but I think I know how to do that part if I can just get the part I've described above to work.)
I've done something like this in Excel and SQL but cannot figure out how to do it using DAX in Power BI desktop. Thank you for your help.
hi, try with this calculated column
FruitTotal = VAR Period = Table1[Period] RETURN CALCULATE ( SUM ( Table1[Quantity] ), FILTER ( Table1, Table1[Category] = "Fruit" && Table1[Period] = Period ) )
8 Replies
you have only these category values in table (Fruit->Apples, Oranges, Bananas etc.) or do you have some another (Vegetable -> tomato.... etc.)? Is there some Parent-Child ID column in table?
Regards.
Pavel
Fruit is the top category. I do have another grouping category between Fruit and the set {Apples, Oranges, and Bananas} called Citrus that will apply to some of the items in the set and not the others (i.e., Oranges but not Apples or Bananas), but I omitted it because I'm trying to keep things as simplified as possible. (I understand this is known as a "ragged hierarchy", i.e., one with an uneven number of levels, which is descibed here: https://msdn.microsoft.com/en-us/library/ms365406.aspx )
I should have mentioned I have accomplished what I am trying to do in SQL with similarly formatted data using windowing functions. I have read that an equivalent to windowing functions is possible with DAX's a combination of RANKX and CALCULATETABLE, likely combined with other functions, but I have not been able to make any of the examples I've seen work with this case.
And I should have also added in reply to your question that there is no Parent-Child ID column in the table.
