cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Continued Contributor

## SUMIFs by subcategory

Here's a link to Excel data: https://www.dropbox.com/s/z9odcbbk1hnhd0i/Power%20BI%20Forum%20Sample.xlsx?dl=0

Here's a link to BI file: https://www.dropbox.com/s/rbjoqspdjdmtquq/Power%20BI%20Forum%20Sample.pbix?dl=0

In powerBI, I'm trying to create a measure to SUM the column [BASE] (BW in Excel) subject to the following filters:

GroupBY [Asset ID] (column B in Excel)

> [Count] (column BC in Excel)

< max[Count] for a given [Asset ID]   (e.g., some [Count]s associated with a given [Asset ID] are 60, some are 72, some are 120)

in Excel, the formula would look roughly like this:

=SUMIFS([Base],[Asset ID],[@[Asset ID]],[Count],">0",[Count],"<"&[@[Project Term (mos)]])

but I haven't figured out a simpler expression for the MAXIFS

1 ACCEPTED SOLUTION
Continued Contributor

This programming seemed to do the trick:

CALCULATE(
SUM('Table'[Base]),
ALLSELECTED('Table'[Asset ID]),
Filter('Table','Table'[Count]>0),
FIlter('Table','Table'[Count]<'Table'[Project Term (mos)]+1)
)
Continued Contributor

This programming seemed to do the trick:

CALCULATE(
SUM('Table'[Base]),
ALLSELECTED('Table'[Asset ID]),
Filter('Table','Table'[Count]>0),
FIlter('Table','Table'[Count]<'Table'[Project Term (mos)]+1)
)

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors