Forum Discussion

nielsrask's avatar
nielsrask
Regular Visitor
9 years ago
Solved

DAX Formula for subtracting columns?

Hi, I have created a matrix table from my data base.

 

Now, I would like to subtract two of the columns from each other and put the result in a new column.

 

Can I do it directly in the visual or is it possible for me to convert my Matrix Table visual to a query?

 

Thanks 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    nielsrask,

    Create the following measures in your table and drag the result measure in your visual.

    totalforspecies = CALCULATE(SUM('EKRN-a482911f-cdbf-4b5f-9e2d-8cde02e7f164-31_05_2017_clean july 2017'[species-count]))

    totalforblankaction = CALCULATE([totalforspecies],'EKRN-a482911f-cdbf-4b5f-9e2d-8cde02e7f164-31_05_2017_clean july 2017'[action-taken]="")

    result = [totalforspecies]-[totalforblankaction]

    For more details, please review the attached PBIX file.

    Regards,
    Lydia

3 Replies

  • Interkoubess's avatar
    Interkoubess
    Icon for Solution Sage rankSolution Sage
    9 years ago

    Hi nielsrask,

     

    If you can five us a sample it will be great.

    If I get what you are talking about, why not using a simple sum but the blank.

    I am looking forward receiving your sample data.

    Thanks,

     

    • nielsrask's avatar
      nielsrask
      Regular Visitor
      9 years ago

      Hi Interkoubess, thanks for your reply, much appreciated!

       

      Here is a sample: https://ascendaps-my.sharepoint.com/personal/nra_ascend_xyz/_layouts/15/guestaccess.aspx?docid=11e43268c372c4e9b993fb50592ef59cf&authkey=AXXJHAMQDq1EKMHqKJEMcyc

       

      As you can see, I have created the Matrix table on page 2 and would like to subtract the 'blank column' from the 'total' column and display the resut in a new column in the matrix table.

       

      I would be happy, if I could just add the matrix table to my queries:-) 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        nielsrask,

        Create the following measures in your table and drag the result measure in your visual.

        totalforspecies = CALCULATE(SUM('EKRN-a482911f-cdbf-4b5f-9e2d-8cde02e7f164-31_05_2017_clean july 2017'[species-count]))

        totalforblankaction = CALCULATE([totalforspecies],'EKRN-a482911f-cdbf-4b5f-9e2d-8cde02e7f164-31_05_2017_clean july 2017'[action-taken]="")

        result = [totalforspecies]-[totalforblankaction]

        For more details, please review the attached PBIX file.

        Regards,
        Lydia