Create IF measure summing one column based on another column

[lsullivan6311]

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.

[lsullivan6311]

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

[lsullivan6311]

 why MAX on a TEXT column?

[Samarth_18]

HI @lsullivan6311 ,

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

[lsullivan6311]

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]

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

[lsullivan6311]

It is not allowing me to load a sample data set

[lsullivan6311]

company ID      PrdType                      Count      dateend

1055                  Unit Production         16438       12/31/2021

1055                  Unit Production         18540        11/30/2021

1055                  Unit Production          18516       10/31/2021

1343                  Unit Production          11029       12/31/2021

1343                  Unit Production          18554       11/30/2021

1343                  Unit Production           10503       10/31/2021

[Samarth_18]

@lsullivan6311 Please refer the attached file.

[lsullivan6311 · Accepted solution]

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.

[lsullivan6311]

I have a followup issue.  The measure now works on the specific row; however, the sum of the column is still the total, it does not consider the measure calculation.  How can I get the sum to match the numbers in the column?