Forum Discussion

CCTADA's avatar
CCTADA
Frequent Visitor
4 years ago

SUM rows with IF clause return number

New user and I thought I was doing good until my data was summed and it wasn't even close. Darnit! 🙂 Yes, I searched but I didn't find what I needed.

 

Column E is where I'd like to create a column and put this formula. I'm basically checking to see if each row has a 1 in columns A, B, C, and D. If it does, enter a 1 in column E otherwise, enter a 0.

 

This is my Excel formula: =IF(SUM(A1:D1)>=1,1,0) result goes into E1 as 1.

 

I initally used a Conditional Column for column E that looked like this: 

If A1 equals 1 then 1 else

if B1 equals 1 then 1 else

if C1 equals 1 then 1 else

if D1 equals 1 then 1 else 0

 

I thought it would work, but it doesn't add things up correctly when I'm creating a graph. All columns are integers and shows me the SUM symbol when I'm creating the report. It's just that Column E seems to be counting the rows and returning 4 instead of adding it up and returning 3. 

 

 ABCDE
11001

1

201001
300000
411111
Need Help

6 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    CCTADA Quick answer I think is to just change this to a SUM: 

     

     

    If that doesn't work, try recreating the column, I did mine in Power Query: 

    In Power Query you could add a new Custom Column: 

     

    if List.Sum({[A], [B], [C], [D]}) >= 1 then 1 else 0

    Then add this to your visuals as SUM.

     

    HOWEVER

    I do wonder what your data represents and if you might benefit from Unpivoting columns A-D so you can more easily count them all together? 

     

    See if this blog post relates at all to your data: https://www.myonlinetraininghub.com/excel-tabular-data-format 

    • CCTADA's avatar
      CCTADA
      Frequent Visitor
      4 years ago

      AllisonKennedy - I think what you gave me here is what I was initially trying to do. I'm trying to test your method and the other one. I looked at the link you included. My data is tabular. Nothing is pivoted within the data. 

      A little more explanation in case I should be doing somethign else: the 1/0's in columns A-D represent if a candidate hit a specific status in our ATS. I'm trying to calculate, did this candidate have a department interview? In order to do that, I have to look at 10 columns (different candidate statuses) and see if they hit any of those statuses (1), then yes, they had a department interview. It's not so much that I'm adding up the column as it is looking to see if there a 1 in any of the columns. In Excel, adding it up was the easiest formula. In Power BI, that may not be the case (but I don't know what I don't know, right?!). Thank you for any feedback/assistance. 


  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi CCTADA ,

     

    You can create a measure in power bi desktop.

    E measure = IF(SUM('Table'[A])+SUM('Table'[B])+SUM('Table'[C])+SUM('Table'[D])>0,1,0)

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • CCTADA's avatar
    CCTADA
    Frequent Visitor
    4 years ago

    Anonymous AllisonKennedy 

    I'm working on adding these both ways. What is the difference between using Power Query and creating a column vs adding a Measure? If there's an article, let me know - noob here so I'm still trying to get through a lot of this basic material while I create things.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi  CCTADA ,

       

      M and DAX are two completely different languages.

       

      M is used in Power Query (a.k.a. Get & Transform in Excel 2016) and the query tool for Power BI Desktop. Its functions and syntax are very different from Excel worksheet functions. M is a mashup query language used to query a multitude of data sources. It contains commands to transform data and can return the results of the query and transformations to either an Excel table or the Excel or Power BI data model.

       

      More information about M can be found here and using your favourite search engine.

       

      DAX stands for Data Analysis eXpressions. DAX is the formula language used in Power Pivot and Power BI Desktop. DAX uses functions to work on data that is stored in tables. Some DAX functions are identical to Excel worksheet functions, but DAX has many more functions to summarize, slice and dice complex data scenarios.

       

      There are many tutorials and learning resources for DAX if you know how to use a search engine. Or start here.

       

      In essence: First you use Power Query (M) to query data sources, clean and load data. Then you use DAX to analyze the data in Power Pivot. Finally, you build pivot tables (Excel) or data visualisations with Power BI.

      Differences between the M Language and DAX in Power BI.

       

      And measure vs calculated column: What is the difference between Power BI calculated columns and measures? - TrueCue.

       

      Reference: powerbi - What's the difference between DAX and Power Query (or M)? - Stack Overflow

                        

       

       

      Best Regards,

      Stephen Tao

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.