cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors