Right approach to summary data?

[markes]

Background

I'm used to complicated Excel formulas but am trying to understand Power BI's approach to things so I can make reports.

I have a big Table of fruits with columns like "type" (e.g. "apple", "pear"), "color" (e.g. "red", "green"), "cost" (e.g. 2.45) with many individual fruits often with the same type (but at most one for any type&color combination like "green apple").

My main report will generally be filtered to a specific color (or maybe a handful of colors, but I could live with just one), and I'll want to see both data that's specific to the filtered color(s) (e.g. cost of yellow apples, cost of yellow bananas) and summarized for the type (e.g. total cost of all apples, number of apple rows).

Question

Basically, what's the right approach for getting that summary data in there?

What I've tried

1. I almost had a SUMMARIZECOLUMNS solution (at least for total cost), but then it didn't filter right in the report (as described in this StackOverflow answer).
2. I can add calculated columns to the original Table, and that "works" but that's redundant as I'd be calculating total cost of all apples for each individual apple.
3. I also tried using DISTINCT instead of SUMMARIZE(COLUMNS) to make a Table with just the list of fruit types and then made a relationship and used a calculated column COUNTROWS(RELATEDTABLE(...)) to show the count of each type of fruit, but again this did not work in the filtered report whether I tried "Don't summarize", "Sum", or "Count", etc.

[markes · Accepted solution]

Much later, I found that measures using "REMOVEFILTERS" like the following solve this problem in the way I intended. For example, something like:

CALCULATE(SUM(MyTable[Cost]), REMOVEFILTERS('MyTable'), VALUES(MyTable[Fruit]))

[v-jingzhang]

@markesAgree that measures will be a better approach to summary data. A measure is evaluated only when used, its results are not stored in the data model and cannot be seen unless you use a measure in a visual. And a measure is evaluated according to the context it is in. You may try putting a measure in a table or matrix or other visuals, and put Color or Type or a combination of them into the same visual to see the change of the measure. You will find that the measure value is not a fixed value and will change automatically to get what you need.

Reference documentations:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-measures

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[mahoneypat]

You probably should just do this with measures instead of new tables or columns.  For example, you can make a simple measure of SUM(Table[Cost]) and use it either in a table visual with both the Color and Type columns, or just the Type column.  I used SUM for example but you could do any aggregation/calculation.  The visual and the column you choose will include the correct rows for the calculation.

Regards,

Pat

[markes]

One approach I found that seems to meet my needs, but maybe isn't best-practice, is to make a reference to the original Table query and use "Group by", as described in @Mi2n 's answer to "SUM rows" .