Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Recreating a Table Visual Total via Measure

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_IDTEAM_IDDATEPLAN_VALUE
11January 202210
12January 20220
21January 20225
22January 20225
31January 20220
32January 202210
41January 20220
42January 20220

 

PLAN 2

PROJECT_IDTEAM_IDDATEPLAN2_VALUE
11January 202210
12January 20228
21January 202210
22January 20228
31January 202210
32January 20228
41January 20223
42January 20223

 

ACTUAL

PROJECT_IDTEAM_IDDATEACTUALS
11January 20228
12January 20229
21January 202211
22January 202210
31January 20228
32January 20227
41January 20225
42January 20224

 

TEAM MASTER

TEAM_IDTEAM_NAME
1Team 1
2Team 2

 

PROJECT MASTER

PROJECT_IDPROJECT_NAME
1Project 1
2Project 2
3Project 3
4Project 4

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

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

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        johnt75
        Super User
        3 years ago

        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 ?

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    3 years ago

    Hi Anonymous ,

     

    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