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.
I am struggling to create a summary measure that works with a tree model specified with left-right index pair.
I simplified the datamodel to demonstrate this: I have two tables, Task and Task item. Task table contain the hierarchy with ProjectID specifying the project and Left and Right specifying the range of TaskIDs that the row sums up for the project. For example row Subcategory 1 has Left 2 and Right 500: this means the row is summary of all rows with TaskID between 2 and 500. Task Item table contain the data to be summed with relation to Task table by ProjectTaskKey which is a combination of ProjectID and TaskID.
What I want is to show the data in a matrix table with sum of Sales from Task Item table on two different levels: a) by ProjectID and b) all TaskID levels with summary of the task and all tasks below it. Without measure I get the right sum on a task and ProjectID level but not on summary levels (Subcategories nor Total).
Here is were I got so far, but this is not working as it shows the total sum of all tasks:
Total Sales =
SUMX(
ALLSELECTED('Task');
CALCULATE(
SUM('Task Items'[Sales]);
FILTER(
ALLSELECTED('Task'[TaskID]);
'Task'[TaskID] >= EARLIER('Task'[Left]) && 'Task'[TaskID] <= EARLIER('Task'[Right])
)
)
)
Hi, @pmk
Based on your description, I created data to reproduce your scenario. You may create a measure as follows:
Total Sales =
SUMX(
'Task',
CALCULATE(
SUM('Task Items'[Sales]),
FILTER(
ALLSELECTED('Task'),
'Task'[TaskID] >= EARLIER('Task'[Left]) && 'Task'[TaskID] <= EARLIER('Task'[Right])
)
)
)
Result:
If I misunderstand the your thoughts, please show me your expected output. I am glad to solve the problem for you.
Best Regards,
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Allan for the reply, however the result is not what I'm looking for. Here is the expected result:
Total Sales should be sum of Sales for the particular task or all tasks within it's Left-Right range. For example Subcategory 1 should be a sum of Task 1 and Task 2.
Do you have Bridge tables linking the fact tables via one-to-many relationships?
Can you upload a sample of your data & tables somewhere so that we can work through the solution?
Proud to be a Super User!
Paul on Linkedin.
Hi, here is the sample file: https://ufile.io/3sqmlh96
There's no bridge tables, only those two tables. Thanks in advance for your effort!
Here you go:
I added a bridge table with new columns to create this model setup:
BTW, what is the [Total Sales New] measure calculating?
Here is the link to the file: Total Sales PBIX file
Proud to be a Super User!
Paul on Linkedin.
Any ideas to solve this are greatly appreciated!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |