Limiting Denominator to only dates where numerator has a value

[QuestionAndAnswer]

Need some help with a formula. 

We have a dashboard that shows KPIs for sales and transactions data by discount code. On this dashboard the users can filter through different discounts and see information such as sales, transactions, unique customers, etc. They also want to see discounted sales as a % of total sales on the days where there was a transaction with that discount code used.

This is where I'm having some trouble. If we just look at calculating the denominator, I need a formula that takes every transaction in the days that there was a transaction for the selected discount codes.

Here's an example of what I'd want:

Date Discount Code Transaction ID Sales

4/24/2025,   Discount-A,   111,    $10

4/24/2025,   N/A,               112,   $15

4/23/2025,   Discount-A,   113,    $12

4/23/2025,   N/A,               114,    $11

4/22/2025,   N/A,               115,    $9

This is what I've tried as a formula for the denominator:

IF(GetSelectedCount([Discount Code])>=1,
sum({$<[Discount Code]=>}Sales)
)

For this I would want the sum of sales for 4/23/2025 and 4/24/2025, excluding the sales from 4/22/2025. So the total should be $48. The formula actually gives me a value of $57. I was thinking I need something like:

Only({<SUM(Sales)>={1}>} Date)

in the set analysis. That should work because this KPI has a condition to only show if a selection is made in the discount filter. When no selection is made all the KPIs on the page are blank (intended).

Any ideas on this?

[krishna20]

Hi @QuestionAndAnswer ,

Are you looking for the solution only for multiple dates? If yes, your expression would be IF(GetSelectedCount([Date])>1,
sum({$<[Discount Code]=>}Sales)
)

[QuestionAndAnswer · Accepted solution]

Hi @krishna20 

Thanks for the suggestion! I ended up finding a way to do it on my own though. Here it is:

IF(GetSelectedCount([Discount Code])>=1,
SUM(AGGR(sum({$<[Discount Code]=>}Sales),[Date]))