Create new table with measures based on row

[JHF]

Hi

Trying to create a new table based on the imported data. Inthe new table I want to create 4 calc columns based on the row content.

I have value column but want to create 4 calc columns based on Estimate and PnL Line.

How do I do that? I am also open for editing in the query editor

 Example attached:

[aldredd]

Another option is to create a new virtual table with aggregation

AggregatedPayments = SUMMARIZE (CALCULATETABLE ('Payments',Payments[TransactionReference] <> ""),
'Payments'[TransactionReference],"TotalPaidToInvoice",SUM ( 'Payments'[AllocationAmount] ))

In my example, I wanted a table showing sum of payments per Transaction, but only where the transaction reference was not blank

This can be broken into two steps...

CALCULATETABLE ('Payments',Payments[TransactionReference] <> "")

This creates a dataset based on the Payments table, filtering out blank references

the table is the the input for a SUMMARIZE function, which performs this aggregation

SUMMARIZE ([Result of CALCULATETABLE],
'Payments'[TransactionReference],"TotalPaidToInvoice",SUM ( 'Payments'[AllocationAmount] ))

This gives me the reference, and the sum of 'Allocation', calling the new field 'Total Paid To Invoice'

This then appears as a new dataset for me to work with (and can create new relationships based on it)

[JHF · Accepted solution]

Hi

I have solved it. The best/easiest/quickest way was to use the "conditional column" function in Query Editor.