cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
fyip Frequent Visitor
Frequent Visitor

SUMing column in reference to a particular column but without filter

Hi

 

I'm stuck in finding the right formula to get the "DESIRED RESULT" table on the dashboard.

 

I have two DB table, 1) Product Table, 2) Booking Table and I want to show today's bookings.

 

Now I want to add the "Total Bookings" column so I can ultimately get the Status column at the end which shows:

Total Bookings Per Product  / Capacity

 

Unfortunately, I can never get the Total Booking correctly, it always filter down back to the same of "Booking".

 

Bookings   |    Total Booked

-----------------------------

1                       1

4                       4

2                       2

1                       1

1                       1

 

 

Whereas I want

 

Bookings   |    Total Booked

-----------------------------

1                       7

4                       9

2                       5

1                       5

1                       7

 

Total Booked = CALCULATE(sum('booking'[Booking]), 'booking'[Product Code] = 'product'[Product Code]))

 

Almost like 

Select *, (Select SUM(Booking) from Booking b where b.ProductCode = ProductCode) as Total Booked  From Booking,

(Select Capacity from Product p where p.ProductCode = b.ProductCode) as Capacity, 

  where Date = DATE(now()) 

 

Can any body give me some advice?

 

sumbooking.jpg 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SUMing column in reference to a particular column but without filter

the way I understand it you want to disregard all the filter context of the reference number, is that correct?
This should work:

CALCULATE(SUM('booking'[Booking]),ALL('booking'[Ref No.]))

it will sum up all the products regardless of their reference numbers, if you also want to disregard date you may add it in similar manner or try this:

CALCULATE(SUM('booking'[Booking]),ALLEXCEPT('booking'[Product Code]))
2 REPLIES 2
quentin_vigne Senior Member
Senior Member

Re: SUMing column in reference to a particular column but without filter

Hi @fyip

 

Add this column to your table 

 

TotalBooked = COUNTROWS(YourTable)/CALCULATE(COUNTROWS(YourTable);ALL(YourTable))

And then choose this type of option

 

example.PNG

If you want the percentage, you only have to change the value to percent in the format part of desktop version

Super User
Super User

Re: SUMing column in reference to a particular column but without filter

the way I understand it you want to disregard all the filter context of the reference number, is that correct?
This should work:

CALCULATE(SUM('booking'[Booking]),ALL('booking'[Ref No.]))

it will sum up all the products regardless of their reference numbers, if you also want to disregard date you may add it in similar manner or try this:

CALCULATE(SUM('booking'[Booking]),ALLEXCEPT('booking'[Product Code]))