Aggregating by date and other three qualitative variables

[doublepsa]

Hi, hope everyone is doing great!

I'm building a line chart and I need to calculate one of the measures using a weighting, but I'm not being able to do it correctly. My dataset have the following variables: faturamento, qtd_cartoes, qtd_cartoes_ativos, flag_controle, emissor, variante, produto, mes_ano.

For a given value of mes_ano (a date, divided by months), what I need is calculating sum(faturamento) when flag_controle = 1 divided by sum(qtd_cartoes) when flag_controle=1 and multiplying this result for sum(qtd_cartoes), this needs to be calculated for each combination of emissor, variante and produto. Afterwards, I need to sum all of these values and divide it by sum(qtd_cartoes). This needs to be calculated for all values of mes_ano, to be displayed as a time series graph.

I tried to use mes_ano as a dimension and to use the following formula in the measure, but it didn't work, the values aren't as expected:

aggr(sum(aggr(sum({<flag_controle = {1}>} faturamento)/sum({<flag_controle = {1}>} qtd_cartoes) * sum(qtd_cartoes), (emissor), (variante), (produto)))/sum(qtd_cartoes), mes_ano)

Thanks in advance!