Results 1 to 9 of 9

Thread: Power BI Measure - calculate on two columns in same table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power BI Measure - calculate on two columns in same table

    Hi there

    I'm trying to create a measure using two columns in the same table..

    I'm trying to sum two columns

    Total days = sum(Table1[Metric]+Table1[Metric2])

    However I'm getting the following DAX message...

    The SUM function only accepts a column reference as an argument.

    I'm new to PowerBi and Dax - can someone please point me in the right direction?

    Much appreciated

    Md

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    Toronto, Canada
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power BI Measure - calculate on two columns in same table

    SUM can only take one column at a time, so you have to use:

    Total Days:=SUM(Table1[Metric])+SUM(Table1[Metric2])

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power BI Measure - calculate on two columns in same table

    Thank you

    I'm still getting the same error message, even when I use multiple sums as below

    Total Days = SUM(Table1[Metric]) + SUM(Table1[Metric2])

    I'm confused - could it be due to both Metric and Metric 2 are Sigma calculations?

    regards

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Location
    Toronto, Canada
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power BI Measure - calculate on two columns in same table

    What do you mean by:
    Quote Originally Posted by M1donne View Post
    could it be due to both Metric and Metric 2 are Sigma calculations?
    Are [Metric] and [Metric2] columns or measures? If they are measures, then you do not need to refer to Table1 or use SUM:
    Total Days:=[Metric]+[Metric2]

  5. #5
    New Member
    Join Date
    Apr 2014
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power BI Measure - calculate on two columns in same table

    Hi there

    They are both columns, the Sigma sign to the right of the column name (in the fields section of the screen) - can you please confirm what that means?

    I appreciate your help

  6. #6
    Board Regular
    Join Date
    Oct 2014
    Location
    Toronto, Canada
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power BI Measure - calculate on two columns in same table

    Quote Originally Posted by M1donne View Post
    Hi there

    They are both columns, the Sigma sign to the right of the column name (in the fields section of the screen) - can you please confirm what that means?

    I appreciate your help
    Alright, so if it has a sigma sign next to the field in the PivotTable field list, then it is a measure, not a column. You cannot use it in a SUM function. If you want to add them together, just use [Metric]+[Metric2]. You'll want to be sure you know what the measures are doing so that you get the expected result.

    I'm assuming you didn't create the measure yourself. There are 2 ways to see what the measure is doing:

    1. If you are in the Excel Window --> Power Pivot ribbon --> Measures --> Manage Measures. Then choose the measure you want to see and hit edit. That will show you the DAX code behind the measure and allow you to edit it.
    2. If you are in the Power Pivot Window, then go to the table where the measure is located (under whichever table it appears in your pivot table field list) and search for it in the grid which appears beneath the table with values. You can then click on it to view and double click to edit just like you would an Excel formula.

    Hope this makes sense.

  7. #7
    New Member
    Join Date
    Apr 2014
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power BI Measure - calculate on two columns in same table

    Thank you so much - my understanding of the Measure was it had a 'Calculator' Icon against it as opposed to a Sigma sign - that makes much more sense.

    Much appreciated - having dabbled in Power Pivot, this is so much better.

  8. #8
    New Member
    Join Date
    Apr 2014
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power BI Measure - calculate on two columns in same table

    I'm working in Power BI as opposed to Power Pivot - does that matter re checking the Measure code?

  9. #9
    Board Regular
    Join Date
    Oct 2014
    Location
    Toronto, Canada
    Posts
    99
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power BI Measure - calculate on two columns in same table

    Sorry. I didn't see the PowerBI part of the thread title. I believe if you click on the measure in the field list, you can view the formula in the formula bar.

    With respect to the calculator icon vs sigma icon, I'm not 100% sure but I believe the calculator is for a calculated column rather than a measure. But don't quote me on it.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •