Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
MXXIV
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
dateproduct typesales
company12023-04-01apples20
company12023-04-01pears6
company12023-04-02pears2
company12023-04-02apples12
company12023-04-03apples

22

 

I want to display it like this:

company nameapplespears
company1548

 

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
DimaMD
Solution Sage
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

View solution in original post

3 REPLIES 3
DimaMD
Solution Sage
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

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

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.