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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fyip
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
Stachu
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

2 REPLIES 2
Stachu
Community Champion
Community Champion

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


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

quentin_vigne
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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