Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

calculating a target

Good Morning

I am trying to calculate a target at the top level, the dax i am using is calculating at row level but then adding this up for the top level.

 

This is the code i am using

Measure =
SUMX('table',
VALUE(MAX(
    'table'[total_applicants]
        )
    )
)
 
What i need to do is in a matrix aggregate this up
Fields in my matrix are
Department
Manager
Course
then the value is total_applicants
 
The courses have an overarching course code and then subcodes, what it is currently doing is adding the subcodes together to get the overarching total.
 
Example:
 
Course Code      total_applicants 
1                           96
1.1                       48
1.2                       48
2                          50
2.1                       25
2.2                       25
 
The overarching total so course code 1 should be the same as the sub codes 1.1 and 1.2 so in the above example 48.
 
Hope that makes sense
 
Maureen
 
 
 
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.
    Sample data:

    Measure:

    Total = IF(HASONEVALUE('Table'[Course Code]),SUM('Table'[Value]),MAX('Table'[Value]))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Aburar_123's avatar
    Aburar_123
    Solution Supplier
    3 years ago

    Hi Anonymous ,

     

    Need some clarity. Are you expecting the result as below?

     

    Course Code      total_applicants 
    1                           48
    1.1                       48
    1.2                       48
    2                          25
    2.1                       25
    2.2                       25
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Good Morning,

       

      yes those are the results i am expecting

       

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.
    Sample data:

    Measure:

    Total = IF(HASONEVALUE('Table'[Course Code]),SUM('Table'[Value]),MAX('Table'[Value]))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi

       

      I am still getting the 96 not the 48