cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JHF
Frequent Visitor

Create new table with measures based on row

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

pivot data.PNG Example attached:

1 ACCEPTED SOLUTION
JHF
Frequent Visitor

Hi

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

View solution in original post

2 REPLIES 2
aldredd
Advocate I
Advocate I

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
Frequent Visitor

Hi

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.