Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create IF measure summing one column based on another column

I need a measure that states:

 

If this Table1[company ID] = “1055”, then

Do this calculation:

 

Calculate(

    SUM(Table1[count]) * 0.50,Table1[PrdType] = "Unit Production")

 

Else do this calculation:

Calculate(

    SUM(Table1[count]),Table1[PrdType] = "Unit Production")

 

 

 

So basically one company production units are 50% and the rest are summed at 100%. 

 

My measure I tried is below:

 

ProdUnits = IF(Table1[company ID]="1055", Calculate(

    SUM(Table1[count])*0.50,Table1[PrdType] = "Unit Production"),
    Calculate(

       SUM(Table1[count]),Table1[PrdType] = "Unit Production"))

 

I get the following error:

 

! A single value for column ‘company ID’ in Table ‘Table1’ cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, count, or sum to get a single result.

 

Please note that company ID format is TEXT, not a number.  I don’t know how I would aggregate a TEXT even if the text is a number.  I cannot provide my tables.

  • Anonymous's avatar
    Anonymous
    4 years ago

    I appreciate your work on this.  This seems to be the same as what I had done yesterday.  The measure seems to be working without errors; however, I do not get any results.  I think I will break the measure apart to understand what part is breaking for my larger tables.  I appreciate the assistance.

10 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion

    HI Anonymous ,

    Please try this:-

    ProdUnits =
    IF (
        MAX ( Table1[company ID] ) = "1055",
        CALCULATE (
            SUM ( Table1[count] ) * 0.50,
            FILTER ( Table1, Table1[PrdType] = "Unit Production" )
        ),
        CALCULATE (
            SUM ( Table1[count] ),
            FILTER ( Table1, Table1[PrdType] = "Unit Production" )
        )
    )
    

    BR,

    Samarth

    • Anonymous's avatar
      Anonymous
      Not applicable

      I tried this measure and although I do not get an error message for the measure, I also do not have any values returned either.  The measure is completely blank in my visualization.

      • Samarth_18's avatar
        Samarth_18
        Icon for Community Champion rankCommunity Champion

        Anonymous Can you please share the sample data in text format with expected output?

  • Anonymous's avatar
    Anonymous
    Not applicable

     why MAX on a TEXT column?

  • Anonymous's avatar
    Anonymous
    Not applicable

    It turns out I had a filter on the table causing my problem, user error!