cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Is it possible to make a measure that sums values on some rows?

I have a table that could look like this:

 company name date product type sales company1 2023-04-01 apples 20 company1 2023-04-01 pears 6 company1 2023-04-02 pears 2 company1 2023-04-02 apples 12 company1 2023-04-03 apples 22

I want to display it like this:

 company name apples pears company1 54 8

There will be one count per product per date and company. The data will also be controlled by a slicer that allows you to select date range. There is a graph, but total sum per range and company is also desired to be seen under the graph.

The data is loaded via Direct Query, I cannot accomplish this using Pivot column feature.  But measures allow me to do a sum or an average for a data source. I can do this for example:

``AverageSales = AVERAGE(my_data_source[sales])``

But I would like to do instead something like:

``apples_average = AVERAGE(my_data_source[sales] IF my_data_source[product type] == "apples")``

In my case, the number of product types is finite, low and guaranteed not to change, so making several measures like this manually would solve my problem.

Is it possible to make a measure that only applies on a subset of rows in the data?

1 ACCEPTED SOLUTION
Solution Sage

@MXXIV try it

``````measure 1 = CALCULATE( SUM('table'[sales]), 'table'[product type] <> "pears")

measure 2= CALCULATE( SUM('table'[sales]), 'table'[product type] = "pears")``````

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
3 REPLIES 3
Solution Sage

@MXXIV try it

``````measure 1 = CALCULATE( SUM('table'[sales]), 'table'[product type] <> "pears")

measure 2= CALCULATE( SUM('table'[sales]), 'table'[product type] = "pears")``````

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Regular Visitor

Thank you very much, this worked even in complicated tables.

Super User

@MXXIV Use a Matrix visual. Put Company on Rows and Product on Columns and a simple sum or average.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors