Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pmk
New Member

Summary measure in a tree model

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.

 

Screenshot 2019-12-10 at 21.04.49.png

 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])
        )
    )
)

 

 

 

6 REPLIES 6
v-alq-msft
Community Support
Community Support

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:

sss.png

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:

Screenshot 2019-12-11 at 12.16.46.png

 

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.

 

PaulDBrown
Community Champion
Community Champion

@pmk 

 

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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!

PaulDBrown
Community Champion
Community Champion

@pmk 

 

Here you go:

 

Sales.JPG

 I added a bridge table with new columns to create this model setup:

 

Model.JPG

 

BTW, what is the [Total Sales New] measure calculating?

 

Here is the link to the file: Total Sales PBIX file 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






pmk
New Member

Any ideas to solve this are greatly appreciated!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.