Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
hi, try with this calculated column
FruitTotal = VAR Period = Table1[Period] RETURN CALCULATE ( SUM ( Table1[Quantity] ), FILTER ( Table1, Table1[Category] = "Fruit" && Table1[Period] = Period ) )
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.
hi, try with this calculated column
FruitTotal = VAR Period = Table1[Period] RETURN CALCULATE ( SUM ( Table1[Quantity] ), FILTER ( Table1, Table1[Category] = "Fruit" && Table1[Period] = Period ) )
Vvelarde, that worked exactly. My sincere thanks to you!
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
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.
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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |