Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Hope this can help.
Best Regards,
Cherry
Hi @3eren ,
Please try the measure below.
Measure = SUMX(FILTER('Table',[Diff]>0),[Diff])
Here is the output.
Hope this can help you.
Best Regards,
Cherry
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
@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.
Hope this can help.
Best Regards,
Cherry
@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!
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |