Forum Discussion

3eren's avatar
3eren
Frequent Visitor
6 years ago
Solved

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.

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    6 years ago

    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

     

8 Replies

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    6 years ago

    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

    • 3eren's avatar
      3eren
      Frequent Visitor
      6 years ago

      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!

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    6 years ago

    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

     

    • 3eren's avatar
      3eren
      Frequent Visitor
      6 years ago

      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!

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        6 years ago

        Hi 3eren ,

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

        Best  Regards,

        Cherry