Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 No | Amount |
170000 | 5000 € |
210000 | 3500 € |
520000 | 1000 € |
520222 | 2000 € |
174000 | 5000 € |
520000 | 8000 € |
550000 | 1000 € |
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
Solved! Go to Solution.
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.
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.
Ok, i have edited my message 😉
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |