SUMing column in reference to a particular column but without filter

[fyip]

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?

 

[Stachu · Accepted solution]

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

[quentin_vigne]

Hi @fyip

Add this column to your table 

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

And then choose this type of option

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