Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Scenario: Column created to calculates sales % based on a specific group of products.
Filter created to HIDE or SHOW products.
When products are HIDDEN the % calculates correctly but when filter has SHOW products the % goes to 100 as its 1/1.
I would like for the column to always stay at the correct value even when drilling down to product level.
FORMULA:
sum(if([Product Family Name] <> 'CARS' ,([Sales QTY)))/Sum([Sales QTY])
FILTER:
SHOW = 100% (stays static)
HIDE = 2% ( obviously this number is not static)
Is there a way to avoid the column to change values as you drill down?
Try this:-
Sum({<Product_family_name-={'Cars'}>}sales_quantity)/Sum({1}sales_quantity)
sum(if([Product Family Name] <> 'CARS', [Sales QTY])) / Sum(TOTAL <[Product Family Name]> [Sales QTY])