HiTrying 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:

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

Create new table with measures based on row | Microsoft Fabric Community

JHF
Advocate I
9 years ago
Hi
I have solved it. The best/easiest/quickest way was to use the "conditional column" function in Query Editor.:smileyhappy:
aldredd
Advocate I
9 years ago
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)