Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Morning all,
I am relatively new to DAX and have scoured the forums but not found anything that quite works yet.
I am trying to sum each record where the Member No is contained within the Hierarchy string e.g. the first Member No occurs in rows 1, 10 and 12 and sums to 23, whereas the 3rd Member No only appears in Hierarchy row 3 so sums to 3.
The excel formula is: SUMIFS([Amount],[Hierarchy],"*"&[Member No]&"*")
I tried adding "ab" to rows 5 & 9 as part of the attempt using the following formula to see if I could get close and then apply the "Like/contains" bit of logic but no luck: CALCULATE(SUM(Table1[Amount]),FILTER(Table1,FIND("ab",Table1[Hierarchy],,0)<>0))
I have also tried CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Member No]=EARLIER(Table1[Member No]))) as a start, also with the idea of getting close and then adding the Hierarchy element, also with no luck.
Any help would be hugely appreciated! Hopefully the description is understandable. Thank you in advance.
Solved! Go to Solution.
Thanks everyone for your assistance with this - I received a response on another post which does exactly what I need, which I have shown below
Column =
SUMX (
FILTER (
ALL ( 'Table' ),
PATHCONTAINS ( 'Table'[Hierarchy], EARLIER ( 'Table'[Member No] ) )
),
'Table'[Amount]
)
Thanks everyone for your assistance with this - I received a response on another post which does exactly what I need, which I have shown below
Column =
SUMX (
FILTER (
ALL ( 'Table' ),
PATHCONTAINS ( 'Table'[Hierarchy], EARLIER ( 'Table'[Member No] ) )
),
'Table'[Amount]
)
@Merc100 in a calculated column
=sumx(filter(table,find(earlier(table[member no]),table[hierarchy],,0),table[amount])
Thanks @HotChilli , I will next time - only reason I didnt was that my source was Excel, which isnt supported as an attachment here
You can simple copy paste the excel table in the message body. Alternatively, you can save the file in onedrive/google drive and share the link.
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Thanks Vivek, I know for next time!
I think this will do it . Please post data (not a picture) for faster answers next time.
ColumnA = VAR _no = TableWW[no]
RETURN
CALCULATE(SUM(TableWW[amount]), FILTER(ALL(TableWW), CONTAINSSTRING(TableWW[hierarchy], _no)))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |