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.

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

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.