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.
So I've been trying to figure this out using the
However, I still can't get it to show what I want.
I am looking to create a table that displays each Company and a roll up of the sales of their children. I think I am stuck at the Sales Amount Simple. I used the formula and it still only gives me the total at the node and doesn't iterate for the totals for the children. What am I doing wrong or is that doesn't work in Power BI?
Solved! Go to Solution.
@ap,
You may refer to measure below.
Measure = VAR c = SELECTEDVALUE ( Sales[Company] ) RETURN CALCULATE ( SUM ( Sales[Sales] ), ALLSELECTED ( Sales ), PATHCONTAINS ( Sales[Path], c ) )
@ap,
You may refer to measure below.
Measure = VAR c = SELECTEDVALUE ( Sales[Company] ) RETURN CALCULATE ( SUM ( Sales[Sales] ), ALLSELECTED ( Sales ), PATHCONTAINS ( Sales[Path], c ) )
Hello, Thanks for this solution very useful.
Your solution work only if one element is selected throuht the slicer.
Do you have an idea , how can we do with more selections ?
Success! Thank you so much Sam!!!! You are the man. However, I have one thing to ask. We also split our account types. So I have a need to filter some of the accounts out. However, if I click on the visual filter to only look at Client A, your measure only counts the sales as the current node.
Measure =
VAR c = SELECTEDVALUE('Hierarchy'[Name ID])
RETURN
CALCULATE(
SUM(Sales[Sales]),
ALLSELECTED(Sales),
PATHCONTAINS('Hierarchy'[ID Path],c)
)
Even though there is an AllSELECTED...doesn't that ignore any filters applied?
Figured it out, I used ALL instead of ALLSELECTED if I am filtering. ALLSELECTED only removes implicit filters that are generated within the same query.
Measure =
VAR c = SELECTEDVALUE('Hierarchy'[Name ID])
RETURN
CALCULATE(
SUM(Sales[Sales]),
ALL(Sales),
PATHCONTAINS('Hierarchy'[ID Path],c)
)
@ap,
Glad to hear that. By the way, you may help accept solution. Your contribution is highly appreciated.
I have an 8 level hierarchy, how do I do the opposite and show a NON-aggregated value in a clustered bar chart but still maintain a hierarchy.
In my data I have each person with their own sales value and this is related to a separate table which has the parent child relationship.
Okay, so I also tried creating a new measure called Amount Including Children.
Amount including Children =
SUMX (
'Hierarchy',
CALCULATE(
SUM(Sales[Sales]),
FILTER(
ALL('Hierarchy'),
PATHCONTAINS('Hierarchy'[ID Path],'Hierarchy'[Name ID])
)
)
)
However, that didn't work either. For each row, I want to see if Name ID exists in the ID Name for the entire table not just at the current row, if it does count it into the Sales total...
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |