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
maurcollins
Helper II
Helper II

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
 
 
 
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @maurcollins 

 

You can try the following methods.
Sample data:

vzhangti_0-1679037223214.png

Measure:

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

vzhangti_1-1679037257313.png

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.

View solution in original post

4 REPLIES 4
v-zhangti
Community Support
Community Support

Hi, @maurcollins 

 

You can try the following methods.
Sample data:

vzhangti_0-1679037223214.png

Measure:

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

vzhangti_1-1679037257313.png

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.

Hi

 

I am still getting the 96 not the 48

 

Aburar_123
Resolver IV
Resolver IV

Hi @maurcollins ,

 

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

Good Morning,

 

yes those are the results i am expecting

 

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.