cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Stachu Super Contributor
Super Contributor

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]))
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Highlighted
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

Stachu Super Contributor
Super Contributor

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]))
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 145 members 1,458 guests
Please welcome our newest community members: