Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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]))
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]))
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |