Forum Discussion

vpastor's avatar
vpastor
Frequent Visitor
2 years ago
Solved

Query with row values

Hello everyone

 

I am desperate and do not know how I can realize the following:

 

I have two tables:

 

G_L Entry (1 table)

GL Account NoAmount
1700005000 €
2100003500 €
5200001000 €
5202222000 €
1740005000 €
5200008000 €
5500001000 €

 

Acc_ Schedule Line (2 table)

Totaling
170|520|174|5200|5500

 

The totaling field is a text string composed of several values, for example (170|520|174|524|5200|5500).

I need to make a formula that is able to read those values and add the "Amount" when the "GL Account No" starts with one of the values we have read from the "Totaling" column.

 

For example this measure return this:

22.000 €

 

Thank you very much to all of you.

 

Regards
Vicente

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi vpastor ,

     

    1. In the power query editor, separate the TOTAL values.

     

    2. Hold down the CTRL key and select all columns, inverse perspective columns.

     

    3.Create measure.

    Measure2 = 
VAR _vtable =
    FILTER (
        CROSSJOIN ( 'Table', 'Table2' ),
        FIND (
            FIXED ( 'Table2'[Value], 0, 2 ),
            FIXED ( 'Table'[GL Account No], 0, 2 ),
            ,
            BLANK ()
        )
            <> BLANK ()
    )
RETURN
    SUMX ( SUMMARIZE ( _vtable, [Amount], [GL Account No] ), [Amount] )

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi vpastor ,

     

    1. In the power query editor, separate the TOTAL values.

     

    2. Hold down the CTRL key and select all columns, inverse perspective columns.

     

    3.Create measure.

    Measure2 = 
VAR _vtable =
    FILTER (
        CROSSJOIN ( 'Table', 'Table2' ),
        FIND (
            FIXED ( 'Table2'[Value], 0, 2 ),
            FIXED ( 'Table'[GL Account No], 0, 2 ),
            ,
            BLANK ()
        )
            <> BLANK ()
    )
RETURN
    SUMX ( SUMMARIZE ( _vtable, [Amount], [GL Account No] ), [Amount] )

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.