Forum Discussion

mrothschild's avatar
mrothschild
Icon for Continued Contributor rankContinued Contributor
7 years ago
Solved

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

 

 

 

 

Need Help
  • mrothschild's avatar
    mrothschild
    7 years ago

    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)
    )

1 Reply

  • mrothschild's avatar
    mrothschild
    Icon for Continued Contributor rankContinued Contributor
    7 years ago

    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)
    )