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
markes
Frequent Visitor

Right approach to summary data?

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.
1 ACCEPTED SOLUTION
markes
Frequent Visitor

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

View solution in original post

4 REPLIES 4
markes
Frequent Visitor

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
Community Support
Community Support

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

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


markes
Frequent Visitor

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

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.