I've been searching endlessly for this to no avail.
I have the following table (can't parse result in Power Query because it is a calculated column):
result | itemsNum | totalAmount |
"id":"301","amount":"10"|"id":"764","amount":"15" | 2 | |
"id":"455","amount":"25"|"id":"908","amount":"75"|"id":"321","amount":"55"|"id":"877","amount":"75"|"id":"989","amount":"60" | 5 | |
"id":"112","amount":"65"|"id":"115","amount":"90"|"id":"119","amount":"80" | 3 | |
"id":"612","amount":"70"|"id":"729","amount":"25"|"id":"854","amount":"15"|"id":"098","amount":"25"|"id":"075","amount":"30"|"id":"181","amount":"30"|"id":"193","amount":"35"|"id":"303","amount":"40" | 8 |
result string form is "id":"[id_value]","amount":"[amount_value]"|"id":"[id_value]","amount":"[amount_value]"|...
What should amountTotal column formula be if I want to sum each individual row item amount?
Solved! Go to Solution.
@poweringthru Try:
totalAmount =
VAR __Path = [result]
VAR __Table0 =
ADDCOLUMNS(
GENERATESERIES( 1, [itemsNum], 1 ),
"__Item", PATHITEM( __Path, [Value] )
)
VAR __Table1 =
ADDCOLUMNS(
__Table0,
"__SubPath", SUBSTITUTE( [__Item], """,""", "|" )
)
VAR __Table2 =
ADDCOLUMNS(
__Table1,
"__SubItem", PATHITEM( [__SubPath], 2 )
)
VAR __Table3 =
ADDCOLUMNS(
__Table2,
"__Value", SUBSTITUTE( SUBSTITUTE( [__SubItem], "amount"":""", "" ), """", "" ) + 0
)
VAR __Result = SUMX( __Table3, [__Value] )
RETURN
__Result
I humbly ask forgiveness from the DAX gods...
@poweringthru Try:
totalAmount =
VAR __Path = [result]
VAR __Table0 =
ADDCOLUMNS(
GENERATESERIES( 1, [itemsNum], 1 ),
"__Item", PATHITEM( __Path, [Value] )
)
VAR __Table1 =
ADDCOLUMNS(
__Table0,
"__SubPath", SUBSTITUTE( [__Item], """,""", "|" )
)
VAR __Table2 =
ADDCOLUMNS(
__Table1,
"__SubItem", PATHITEM( [__SubPath], 2 )
)
VAR __Table3 =
ADDCOLUMNS(
__Table2,
"__Value", SUBSTITUTE( SUBSTITUTE( [__SubItem], "amount"":""", "" ), """", "" ) + 0
)
VAR __Result = SUMX( __Table3, [__Value] )
RETURN
__Result
I humbly ask forgiveness from the DAX gods...
It works perfectly, thank you very much!
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
108 | |
75 | |
66 | |
50 | |
48 |
User | Count |
---|---|
164 | |
87 | |
77 | |
70 | |
67 |