Recreating a Table Visual Total via Measure

[marcel97]

Hello all,

in my organization's project management we compare different plan values and actual values of our projects. Projects that do not have one particular plan value are of particular interest.

In a first step I have created a table visual:

Here I could simply filter for projects where PLAN_VALUE is 0, as those projects are the most interesting to me.  I then get the totals of all PLAN2 and ACTUAL values of the projects, that do not have a PLAN_VALUE

I now need to recreate these totals through a measure and this is where I run into problems. As the table visual above sources data from multiple data tables, I cannot make a simple SUMX or CALCULATE calculation work:

Now I need measures that return:

- The totals of PLAN2_VALUE of all projects, where PLAN_VALUE is 0 (would be 6 in this example)

- The totals of ACTUALS of all projects, where PLAN_VALUE is 0 (would be 9 in this example)

Is there a way to achieve this?

Thanks and kind regards

Marcel

Please find attached the RAW DATA:

PLAN 1

PROJECT_ID	TEAM_ID	DATE	PLAN_VALUE
1	1	January 2022	10
1	2	January 2022	0
2	1	January 2022	5
2	2	January 2022	5
3	1	January 2022	0
3	2	January 2022	10
4	1	January 2022	0
4	2	January 2022	0

PLAN 2

PROJECT_ID	TEAM_ID	DATE	PLAN2_VALUE
1	1	January 2022	10
1	2	January 2022	8
2	1	January 2022	10
2	2	January 2022	8
3	1	January 2022	10
3	2	January 2022	8
4	1	January 2022	3
4	2	January 2022	3

ACTUAL

PROJECT_ID	TEAM_ID	DATE	ACTUALS
1	1	January 2022	8
1	2	January 2022	9
2	1	January 2022	11
2	2	January 2022	10
3	1	January 2022	8
3	2	January 2022	7
4	1	January 2022	5
4	2	January 2022	4

TEAM MASTER

TEAM_ID	TEAM_NAME
1	Team 1
2	Team 2

PROJECT MASTER

PROJECT_ID	PROJECT_NAME
1	Project 1
2	Project 2
3	Project 3
4	Project 4

[v-henryk-mstf]

Hi @marcel97 ,

Whether the advice given by @johnt75  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[johnt75 · Accepted solution]

Create a measure like

Plan Value by Project = SUMX( 'Project master', SUMX( RELATEDTABLE('Plan 1'), 'Plan 1'[Plan value]))

which you can then use in a filter, like 

Plan 2 value =
SUMX (
    FILTER ( 'Project master', [Plan value by project] = 0 ),
    SUMX ( RELATEDTABLE ( 'Plan 2' ), 'Plan 2'[Plan value 2] )
)

[marcel97]

Hey @johnt75 ,

this solution works if I include it in the table visual:

However, if I want to include it in a column chart, it does not work and shows up as blank

This is exactly the purpose for which I need the calculation. Is there a way to do it?

Thanks a lot and kind regards

Marcel

[johnt75]

The measures I wrote work for me

For some reason your measures are returning blank in the total row of the table, whereas mine returns the correct total. Are you applying any extra filters to the visuals ?

[marcel97]

@johnt75 my bad! I had the measure included in Plan 2 Value as a variable. Having two separate measures as you intially proposed did the trick! Thanks a lot fpr the quick support and your insight!

Kind regards

Marcel