cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmk Frequent Visitor
Frequent Visitor

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
pmk Frequent Visitor
Frequent Visitor

Re: Summary measure in a tree model

Any ideas to solve this are greatly appreciated!

 

Microsoft v-alq-msft
Microsoft

Re: Summary measure in a tree model

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.

Highlighted
pmk Frequent Visitor
Frequent Visitor

Re: Summary measure in a tree model

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.

 

Super User I
Super User I

Re: Summary measure in a tree model

@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?




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
Thank you!

Proud to be a Super User!




pmk Frequent Visitor
Frequent Visitor

Re: Summary measure in a tree model

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!

Super User I
Super User I

Re: Summary measure in a tree model

@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 

 




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors