Forum Discussion

lsihui_'s avatar
lsihui_
Frequent Visitor
1 year ago
Solved

Create new rows based on values from several rows

Hi,    Would like to check if it is possible to create new rows based on the value of existing rows? If yes, please advise how I can do this. Thank you!    Sample below:  Row 12-17: Available i...
Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello lsihui_,

     

    Can you please try this approach:

    NewTable = 
UNION (
    'ExistingTable',
    ADDCOLUMNS (
        FILTER ( 'ExistingTable', 'ExistingTable'[Property] IN { "Apricot", "Blackcurrant" }),
        "Property", IF ( 'ExistingTable'[Property] = "Apricot", "Combined A", "Combined B" ),
        "Value",
            VAR GroupA = CALCULATE( SUM('ExistingTable'[Value]), 'ExistingTable'[Group] = "A" )
            VAR GroupB = CALCULATE( SUM('ExistingTable'[Value]), 'ExistingTable'[Group] = "B" )
            RETURN 
                IF ( 'ExistingTable'[Property] = "Apricot", GroupA, GroupB ) - 'ExistingTable'[Value]
    )
)

     

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
1 year ago

Hello lsihui_,

 

Can you please try this approach:

NewTable = 
UNION (
    'ExistingTable',
    ADDCOLUMNS (
        FILTER ( 'ExistingTable', 'ExistingTable'[Property] IN { "Apricot", "Blackcurrant" }),
        "Property", IF ( 'ExistingTable'[Property] = "Apricot", "Combined A", "Combined B" ),
        "Value",
            VAR GroupA = CALCULATE( SUM('ExistingTable'[Value]), 'ExistingTable'[Group] = "A" )
            VAR GroupB = CALCULATE( SUM('ExistingTable'[Value]), 'ExistingTable'[Group] = "B" )
            RETURN 
                IF ( 'ExistingTable'[Property] = "Apricot", GroupA, GroupB ) - 'ExistingTable'[Value]
    )
)