Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LandonDodge
Employee
Employee

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.

 

CategoryPeriodQuantity
Fruit20160120
Fruit20160219
Fruit20160320
Fruit20160418
Apples2016013
Apples2016022
Apples2016033
Apples2016044
Oranges20160110
Oranges20160211
Oranges20160310
Oranges2016049
Bananas2016017
Bananas2016026
Bananas2016037
Bananas2016045

 

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): 

 

CategoryPeriodQuantityFruitTotal
Fruit2016012020
Fruit2016021919
Fruit2016032020
Fruit2016041818
Apples201601320
Apples201602219
Apples201603320
Apples201604418
Oranges2016011020
Oranges2016021119
Oranges2016031020
Oranges201604918
Bananas201601720
Bananas201602619
Bananas201603720
Bananas201604518

 

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.

1 ACCEPTED SOLUTION

@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 )
    )



Lima - Peru

View solution in original post

8 REPLIES 8
PavelR
Solution Specialist
Solution Specialist

@LandonDodge

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.

@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 )
    )



Lima - Peru

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

@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

 

another.png




Lima - Peru

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.

@LandonDodge

 

A different approach of solutions:

 

- You can avoid in creation of your table (Summarize) the total Fruit and keep only the categories data

 

- Use a Page or Visual Filter to exclude the Fruit Category.

 

 

 

 

 

 




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.