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.
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
-- 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)
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.
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!
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.