Forum Discussion

LandonDodge's avatar
LandonDodge
Icon for Microsoft Employee rankMicrosoft 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...
Need Help
  • Vvelarde's avatar
    Vvelarde
    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 )
    )
LandonDodge's avatar
LandonDodge
Icon for Microsoft Employee rankMicrosoft 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's avatar
Vvelarde
Icon for Community Champion rankCommunity 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 )
    )
  • LandonDodge's avatar
    LandonDodge
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Vvelarde, that worked exactly.  My sincere thanks to you!

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      9 years ago

      LandonDodge

       

      Another without creating this calculated column is:

       

      Create a Measure:

       

      %Fruit =
DIVIDE (
    CALCULATE (
        SUM ( Table1[Quantity] ),
        ALLEXCEPT ( Table1, Table1[Category], Table1[Period] )
    ),
    (
        CALCULATE ( SUM ( Table1[Quantity] ), ALLEXCEPT ( Table1, Table1[Period] ) )
            - CALCULATE (
                SUM ( Table1[Quantity] ),
                FILTER ( ALLEXCEPT ( Table1, Table1[Period] ), Table1[Category] = "Fruit" )
            )
    )
)

      And put a Matrix Visual

       

      • LandonDodge's avatar
        LandonDodge
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Thank you, Vvelarde.  Peeling back the onion layer a bit, the table I'm working from is actually a calculated table I created with the SUMMARIZE function, referencing a much more detailed table with details about each individual apple, orange, and banana purchased on invidual days making up each month.  Apparently because of this, when I implement either of these solutions with my actual data, Power BI keeps wanting to consider all the Fruit in the aggregate, giving me back only the single aggregated number for all Fruit.

         

        Is there a way for Power BI to "forget" that the calculated table created by the SUMMARIZE function is based on another table?  Then I believe these solutions will work with my actual data as well as they work with my fit-for-public-consumption fruit data.