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
3eren
Frequent Visitor

Summarize DAX

Hi guys,

My first post here

I have this matrix visual, where all values are sums with different filters applied. Please note that the layout is Danish where dot, not comma, is the thousand separator 😊

summarize.png

Diff is calculation of Sale subtracted from Budget. The catch is that I only want values greater than 0 (as seen in Diff2) but my challenge is that I can’t figure out how to make the Total calculation ignore the values lesser than 0 (565.598+534.778+375.341+935.964+267.059+2.941.313+1.416.530 = 7.036.583

 

I’ve tried with a lot of variations of sumx/summarize formulas, but I haven’t had any luck yet.

 

Any of you have a take on this? Any help would be highly appreciated.

1 ACCEPTED SOLUTION

Hi @3eren ,

Your sharing method is right.

By my test with your sample file, you could try the measure below.

Diff3 = SUMX(FILTER('No',[Diff]>0),[Diff])

Here is the output.

Capture.PNG

Hope this can help.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
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

8 REPLIES 8
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @3eren ,

Please try the measure below.

Measure = SUMX(FILTER('Table',[Diff]>0),[Diff])

Here is the output.

Capture.PNG

Hope this can help you.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherry,

 

Thank you for your swift reply. 

 

As I just replied Anthony, the two first columns, Budget and Sale, are measures themselves, so apparently the calculation should be done some other way. I'm sorry that I didn't provide this crucial information in my original post. Sorry about that!

Hi @3eren ,

You could only give the test pbix which could reproduce your scenario so that I could give further help.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


@v-piga-msft wrote:

Hi @3eren ,

You could only give the test pbix which could reproduce your scenario so that I could give further help.

Best  Regards,

Cherry


Hi @v-piga-msft ,

I'd be happy to but I'm very embarresed to admit that I can't find the 'Upload file to post' option anywhere. I see the upload photos, videos, add links, etc., but no option to upload file. 😞 Please forgive, as said, this is my first thread here and I'm still learning.

 

EDIT: I've shared the file in my OneDrive: https://initiatedk-my.sharepoint.com/:u:/g/personal/ckr_initiate_dk1/EXBIu43GG2xDkz_kFYh-4zUBy0vP_H3...

 

Hi @3eren ,

Your sharing method is right.

By my test with your sample file, you could try the measure below.

Diff3 = SUMX(FILTER('No',[Diff]>0),[Diff])

Here is the output.

Capture.PNG

Hope this can help.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-piga-msft  that's it! Thank you so much. You just made my day!

Your solution is so amazingly simple that it almost hurts that I didn't manage to come up with it myself 😄

 

Cheers!

AnthonyTilley
Solution Sage
Solution Sage

you can use a veriable to store the value of your measure and then use an if statment to check if it is less than 0 

below i have defined a veriable "d" that stores the value of budget - sales (your diff one measure)

i then have a veriable called "ret" that performs the if statment to check

the image below shows that the value for -49.303 is no longer included

Measure = 
-- put your measure into a veriable
var d = ([BUDGET]-[SALES])
-- create a second verable where you can check if the value is less than zero if true return blank if false return the orginal value
var ret = if(d<0,blank(),diff)

return ret

den.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Dear Anthony,

Thank you so much for your swift reply. 

 

According to your image, it seems that the calculation is still wrong. Please note that the total should be 7.036.583 - not 6.987.280. When typing in your measure I do get negative values filtered out, but the total still is a sum of all values, hence wrongly 6.987.280.

 

In my original post I didn't state that the Budget and the Sale columns are both measures themselves. I should have mentioned that before. My apologies for that. 

 

Best wishes!

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.