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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculating totals without row context limiting data

I've been attempting to find a solution for this for a while now and haven't been able to.

 

I'm trying to get the values for the Equipment_Days2 column to read:

14    associated with equipment_qty of 4                 (Only including days with qty 4)

17    associated with equipment_qty of 1                 (Including days with qty 4, 2 or 1)

15    associated with equipment_qty of 2                 (Including days with qty 2 or 4)

 

 

Terence2021_0-1636670022047.png

 

Underlying Data

Terence2021_1-1636670109851.png

 

The ALL filter functionality below does not appear to be stripping away the qty row filter in the calculation so that all of the rows associated with the equipment are included in the sum for DaysValue.  How can this be done?

 

Equipment_Days2 = Calculate(SUM(CalculatedDaysTable[DaysValue]),FILTER( ALL(CalculatedDaysTable), CalculatedDaysTable[job_no] == earlier(CalculatedDaysTable[job_no]) && CalculatedDaysTable[Equipment No and Description] == Earlier(CalculatedDaysTable[Equipment No and Description]) && CalculatedDaysTable[date_ASCII] == Earlier(CalculatedDaysTable[date_ASCII]) && CalculatedDaysTable[equipment_uom] == "Daily" && CalculatedDaysTable[equipment_qty] <= Earlier(CalculatedDaysTable[equipment_qty])))
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

This might get you a bit closer.

Equipment_Days2 =
VAR CurrQty = CalculatedDaysTable[equipment_qty]
RETURN
    CALCULATE (
        SUM ( CalculatedDaysTable[DaysValue] ),
        CalculatedDaysTable[equipment_qty] <= CurrQty,
        CalculatedDaysTable[equipment_uom] = "Daily"
    )

 

You used ALL on the entire table, which removes the context from all columns. I'd recommend only using it for specific columns unless you really do want to get rid of all the filtering.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Unfortunately this results in the exact same output as I've been continuously getting, with the additions not being cumulative.

 

Anonymous
Not applicable

This formulation did work once it was converted to a measure.

 

Equipment_Days = VAR CurrQty = max(CalculatedDaysTable[equipment_qty])
RETURN
CALCULATE (
SUM (CalculatedDaysTable[DaysValue]),
CalculatedDaysTable[equipment_qty] >= CurrQty &&
CalculatedDaysTable[equipment_uom] = "Daily"
)
 
Thank you for pointing me in the right direction.

If you're trying to do this as a calculated column, then you have to get the context exactly right. Either remove filters on all the columns you don't want or remove all filters except for a specific set of columns you do want.

 

I can't tell what all columns you have in your table, so I can't do much more than an educated stab in the dark and you'll have to adjust it to fit your particular table.

AlexisOlson
Super User
Super User

This might get you a bit closer.

Equipment_Days2 =
VAR CurrQty = CalculatedDaysTable[equipment_qty]
RETURN
    CALCULATE (
        SUM ( CalculatedDaysTable[DaysValue] ),
        CalculatedDaysTable[equipment_qty] <= CurrQty,
        CalculatedDaysTable[equipment_uom] = "Daily"
    )

 

You used ALL on the entire table, which removes the context from all columns. I'd recommend only using it for specific columns unless you really do want to get rid of all the filtering.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.