sum based on filters in a related table

[Anonymous]

Hi,

I've got a table containing information about reservations (of apartments for sale), and another one containing the history of each reservation, detailing which status a reservation had at any point in time. Both tables ('reservas' and 'reservas_historicos_situacoes', respectively) are related on 'idreserva'.

'reservas' is linked to a calendar table on a date field, 'reservas_historicos_situacoes' is linked to a different calendar table on a different date field. 

Using the following measure, I can get the list of reservations that at some point were in the status 'contract in elaboration (confecção de contrato)'. 

CALCULATETABLE (
                DISTINCT ( 'reservas_historicos_situacoes'[idreserva] ),
                'reservas_historicos_situacoes'[para_nome] = "Confecção de contrato",
                'reservas'[idimobiliaria] = "3"
            )

What I want to do is to sum the contract value in 'reservas' for the reservations of the list above, but when I try something like:

        SUMX (
            CALCULATETABLE (
                DISTINCT ( 'reservas_historicos_situacoes'[idreserva] ),
                'reservas_historicos_situacoes'[para_nome] = "Confecção de contrato",
                'reservas'[idimobiliaria] = "3"
            ),
            'reservas'[valor_contrato]
        )

I get the error: a single value for 'valor_contrato' could not be determined. 

I'd apreciate your help...

[TomMartens]

Hey @Anonymous ,

this error occurs as the column "'reservas'[valor_contrato]" is not in the table that will be iterated. if the column is on the one side of a relationship you can use RELATED( ... ).

If the column is on the many side of a relationship you have to use an aggregation function like so SUM( 'reservas'[valor_contrato]).

If this does not help, please consider creating a pbix that contains sample data but still reflects your data model, upload the file to onedrive or dropbox and share the link. If you use Excel to create the sample data, share the xlsx as well.

Hopefully, this provides an idea of how to tackle the challenge you are facing.

Regards,

Tom

[Anonymous]

Hi Tom, 

thanks for your reply. I'm sorry if this is really basic stuff, but I don't understand what is going on. As I said, I'm trying to find the sum of values ('reservas'[valor_contrato]) for a given period. Reservas is on the 1 side of the relationship with reservas_historico_situacoes. So for every entry in reservas_historico_situacoes, I have only 1 related value, right? Still, using RELATED doesn't work, I get the message 'The column 'reservas[valor_contrato]' either doesn't exist or doesn't have a relationship to any table available in the current context.'

Using an aggregator works, but gives me incorrect results, somehow it aggregates over more than one value. 

I created a sample pbix: shorturl.at/bfEF2.

Thanks a lot!

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Try the following formula:

measure = 
CALCULATE( 
    SUM(reservas[valor_contrato]),
    'reservas_historicos_situacoes'[para_nome] = "Confecção de contrato",
    'reservas'[idimobiliaria] = 3
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.