cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Reply
Highlighted
3eren Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

Re: Summarize DAX

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
AnthonyTilley Established Member
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

den.png

Community Support Team
Community Support Team

Re: Summarize DAX

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

Re: Summarize DAX

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!

3eren Frequent Visitor
Frequent Visitor

Re: Summarize DAX

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!

Community Support Team
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.
3eren Frequent Visitor
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...

 

Community Support Team
Community Support Team

Re: Summarize DAX

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

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoftโ€™s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,256)