Sum field based on a measure

[mlundborg]

Hi,

I have the following data: Revenue, Profit, Customer and YearMonth.

I need to show the combined Revenue and count of Customers in a given month (Dimension in my chart) that has a margin (Profit / Revenue) below a given threshold. The threshold is controlled by a slider, who's value is stored in a variant (vSlider).

I have tried different variations of formulas with aggr, rangesum, if etc. without luck.

One example:

sum(if(aggr(sum([Profit])/sum([Revenue]), Customer, YearMonth)<=vSlider,[Revenue]))

Can someone help me crack this?

Thanks in advance

[YoussefBelloum]

Hi,

try to attach sample app or sample file

[rubenmarin1 · Accepted solution]

Hi Martin, in that expression the check with vslider should be inside the aggr:

sum(aggr(if(sum([Profit])/sum([Revenue])<=vSlider,[Revenue]), Customer, YearMonth))

If doesn't works a sample would be a good idea to make some tests.

[mlundborg]

Thanks rubenmarin‌

It worked, I only had to add a sum to the [Revenue] in the if statement. So it ended up looking like this:

sum(aggr(if(sum([Profit])/sum([Revenue])<=vSlider,sum([Revenue])), Customer, YearMonth))