Forum Discussion

PVVBl00's avatar
PVVBl00
Icon for Helper III rankHelper III
4 years ago
Solved

Join values to single row

Hello everyone                                   I would like to know if in power BI there is any function or how I should do so that of these 3 columns I only leave the first one with the values of...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    Try a calculated table:

    ADDCOLUMNS (

        SUMMARIZE (
            TableName[NUM ID],

            TableName[Type]
        ),

        "Col1", CALCULATE ( SUM ( TableName[1] ) ),

        "Col2", CALCULATE ( SUM ( TableName[2] )),

        "Col1+2", CALCULATE ( SUM ( TableName[1+2] ))

    )

     

    Alternatively you could use group by in power query.

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi PVVBl00 

first you to have a unique date or index column. If don't you need to create an index column using power query. Then for the 3rd column the code would be

1 + 2 =
VAR CurrentIndex = Date[Index]
VAR CurrentTable =
    CALCULATETABLE ( Data, ALLEXCEPT ( Date, Data[NUM ID], Data[TYPE] ) )
VAR FirstIndex =
    MINX ( CurrentTable, Data[Index] )
VAR TotalValue =
    SUMX ( CurrentTable, Data[1] + Data[2] )
RETURN
    IF ( CurrentIndex = FirstIndex, TotalValue, 0 )