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
Anonymous
Not applicable

Measure that sum with filter after aggregation

Hello everyone,

 

Here is my problem:

 

I need to create a measure that sum all the value greater than 0.05 after aggregation.

 

I have a table like this:

 
NameValue
A0.05
A0.01
A0.02
B0.01
B0.01
B0.02
C0.01
C0.04
C0.02

 

The aggregation looks like this but I don't want to create a new table for this:

NameValue
A0.08
B0.04
C

0.07

 

The measure should return 0.15 because only A and C are greater than 0.05.

 

Can this be done in DAX without creating a new aggregated table?

 

Thank you for the incoming answers,

 

David

2 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

This should do the trick

Measure = 
SUMX( 
    VALUES( 'Table'[Name] ),
    CALCULATE( 
        VAR __sum = SUM( 'Table'[Value] )
        RETURN IF( __sum > 0.05, __sum ) 
    ) 
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

View solution in original post

@Anonymous oops, try this

 

Measure 2 = 
 
SUMX ( 
VALUES ( Test[Name] ), 
VAR __s = CALCULATE( SUM ( Test[Value] ) ) 
RETURN IF ( __s > 0.05, __s ) 
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

This should do the trick

Measure = 
SUMX( 
    VALUES( 'Table'[Name] ),
    CALCULATE( 
        VAR __sum = SUM( 'Table'[Value] )
        RETURN IF( __sum > 0.05, __sum ) 
    ) 
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.
parry2k
Super User
Super User

@Anonymous use this measure 

 

Sum over value = 
VAR _s = SUM ( Test[Value] ) 
RETURN  
SUMX ( 
FILTER( VALUES ( Test[Name] ), _s > 0.05 ), 
_s 
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi parry2k,

 

Thanks for your time.

 

The result is not what I expect. The measure gives 0.57 (which is 3 times the total value)

@Anonymous oops, try this

 

Measure 2 = 
 
SUMX ( 
VALUES ( Test[Name] ), 
VAR __s = CALCULATE( SUM ( Test[Value] ) ) 
RETURN IF ( __s > 0.05, __s ) 
)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.