cancel
Showing results for
Did you mean:
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 😊

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

Accepted Solutions
Highlighted
Community Support Team

## Re: Summarize DAX

Hi @3eren ,

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

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

Here is the output.

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.
8 REPLIES 8
Established Member

## Re: Summarize DAX

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```

Community Support Team

## Re: Summarize DAX

Hi @3eren ,

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

Here is the output.

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.
Frequent Visitor

## Re: Summarize DAX

Dear Anthony,

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!

Frequent Visitor

## Re: Summarize DAX

Hi Cherry,

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!

Community Support Team

## Re: Summarize DAX

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.
Frequent Visitor

## Re: Summarize DAX

@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...

Highlighted
Community Support Team

## Re: Summarize DAX

Hi @3eren ,

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

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

Here is the output.

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.
Frequent Visitor

## Re: Summarize DAX

@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!

Announcements