Forum Discussion

Icon for Microsoft Employee rank

Microsoft Employee

9 years ago

Solved

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 Q...

9 years ago

hi, try with this calculated column

FruitTotal =
VAR Period = Table1[Period]
RETURN
    CALCULATE (
        SUM ( Table1[Quantity] ),
        FILTER ( Table1, Table1[Category] = "Fruit" && Table1[Period] = Period )
    )

Icon for Solution Specialist rank

Solution Specialist

9 years ago

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

LandonDodge
Microsoft Employee
9 years ago
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.
- LandonDodge
  Microsoft Employee
  9 years ago
  And I should have also added in reply to your question that there is no Parent-Child ID column in the table.
  - Vvelarde
    Community Champion
    9 years ago
    LandonDodge
    
    hi, try with this calculated column
    
    FruitTotal = VAR Period = Table1[Period] RETURN CALCULATE ( SUM ( Table1[Quantity] ), FILTER ( Table1, Table1[Category] = "Fruit" && Table1[Period] = Period ) )

Related content