none
Conditional SUM ISBLANK RRS feed

  • Question

  • Hi,
    I am getting the data model using a mssql query, if an invoice is canceled the "Estatus" column has the word "Canceled" if not, the column is empty. I am trying to get the sum of sales using this measure:

    Venta:=IF ( ISBLANK(Consulta[Estatus]), SUM(Consulta[Ventas]), 0 )

    but I have an error with this description "a unique value cannot be determined for the 'Estatus' column in the 'Consulta' table, this can happen when a measure formula refers to a column that contains many values ​​without specifying an aggregation such as mix, max, count to get a unique result.

    Why am I getting this error and how can I create the measurement correctly?

    I appreciate your help.

    Regards,

    Javier


    • Edited by jparada Friday, June 19, 2020 5:50 PM
    Friday, June 19, 2020 5:50 PM

Answers

  • I can't really tell from the screenshot if those values are blank or empty strings, but judging by the result from the measure they are probably empty strings so the following alteration might work

    Venta:=CALCULATE ( SUM(Consulta[Ventas]),  Consulta[Estatus] = "" )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by jparada Sunday, June 21, 2020 4:55 AM
    Saturday, June 20, 2020 11:33 PM
    Moderator

All replies


  • Why am I getting this error and how can I create the measurement correctly?

    So if you read the following article it explains why you are getting the error, but note I would not use IF( VALUES() ) solution they propose as that pattern will only work if EStatus is "visible" or used somewhere in your query

    https://powerpivotpro.com/2011/03/the-magic-of-ifvalues/

    A better approach to this sort of SUMIF style calculation is to use the CALCULATE to modify the filters in which a measure is evaluated. So something like the following should work for you. 

    Venta:=CALCULATE ( SUM(Consulta[Ventas]),  ISBLANK(Consulta[Estatus]) )


    http://darren.gosbell.com - please mark correct answers

    Friday, June 19, 2020 11:39 PM
    Moderator
  • Hi Darren,

    I appreciate your answer, thanks for the link to document, the way to evaluate the measure with CALCULATE no longer displays the mentioned error, but I am not obtaining the sum of sales, I am attaching a reference image.


    Could you give me some extra help.

    I appreciate your time.

    Regards

    Javier

    Saturday, June 20, 2020 2:01 PM
  • I can't really tell from the screenshot if those values are blank or empty strings, but judging by the result from the measure they are probably empty strings so the following alteration might work

    Venta:=CALCULATE ( SUM(Consulta[Ventas]),  Consulta[Estatus] = "" )


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by jparada Sunday, June 21, 2020 4:55 AM
    Saturday, June 20, 2020 11:33 PM
    Moderator
  • Excel 365 Pro Plus with PowerPivot and Power Query.
    Neither measure
    CALCULATE ( SUM(Consulta[Ventas]),  ISBLANK(Consulta[Estatus]) ) nor
    CALCULATE ( SUM(Consulta[Ventas]),  Consulta[Estatus] = "" )
    will work since the condition produces a TRUE/FALSE,
    not a Filter function.
    I took the trouble to write a test example,
    where this mistake is automatically flagged.
    http://www.mediafire.com/file/esceifipzto8ebj/06_20_20.xlsx/file
    http://www.mediafire.com/file/405ia4tt90gf6n1/06_20_20.pdf/file

    Sunday, June 21, 2020 12:35 AM
  • Hi Darren,
    I appreciate your help, this worked.

    Regards

    Javier

    Sunday, June 21, 2020 4:54 AM
  • Hi Herbert,
    I also did the test with the measure as you indicate and got empty,

    for now it is resolved as I commented in my previous reply.

    Regards


    Javier

    Sunday, June 21, 2020 4:58 AM