Forum Discussion
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.
- Anonymous4 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
Community 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
- AnonymousNot 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
Community Champion
Anonymous Can you please share the sample data in text format with expected output?
- AnonymousNot applicable
why MAX on a TEXT column?
- AnonymousNot applicable
It turns out I had a filter on the table causing my problem, user error!