Trying to concatenate a value together with a calculated column as text but not successful

[descrt]

I tried to build this table with the following M query.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc9BC4IwFAfwryI7G2ymRUcd4UkoF3QQD0MfJMSErQ59+6YON7V1ePDGj//7s6pC6bvtUIjSgIHsQOmVDINRHc54XeLBtZJZjPTELsJL9sKkog3s8pvfSmYOTpYVd73vA+V2JQ4llsiKjpbwcGSkSy9V8wD9pvwDQoDReKUFb7jwJE+EmLpJCpANtGNRGtAnV8rH2cz4F9OZ7TcdPi+Ob5j9T+fr7voL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t, Series = _t, #"2dooravailable" = _t, ModelTotal = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", type text}, {"Series", type text}, {"2dooravailable", Int64.Type}, {"ModelTotal", Int64.Type}})
in
#"Changed Type"

I wanted to be able to list the number of 2-door models available by brand against each brand's total number of models. (As shown in step 2) so I created a measure 

MeasureBrandTotal = COUNTROWS(RELATEDTABLE(CarBrands))

and it seems to work.

But when I tried to create a column for step 3, the results were not what I expected.

ColumnNumDen = [2dooravailable] & "/" & [MeasureBrandTotal]

I think this is because I am mixing measures and calculated columns but I don't know how to solve this. Can anyone please advise me?

Thanks
Dezzie

[rubayatyasmin]

Hi, @descrt 

It seems like you are trying to concatenate a calculated column and a measure within a new calculated column, which might cause an issue. In Power Query and Power BI, measures are calculated based on the current context of the report, such as the filters or visuals being used. However, calculated columns are generated during data loading and processing, meaning that they are context-independent and stored in memory.

If you're trying to create a calculated column that references a measure, you might be finding that the column doesn't reflect changes in your report's context.

That being said, to get the correct result, you might have to change the measure into a calculated column as well.

[descrt]

Can you share with me how I can do that? I read about how the calculated column has row context but if I try to do a SUM of the total number of models, it just adds up the total for the whole table instead of groups of brands. I looked into GROUPBY, CURRENTGROUP and SUMX but I can't seem to figure that part out.

[rubayatyasmin]

sure. 

1. Go to the Data view in Power BI Desktop.

2. Select the Cars table in the Fields pane.

3. On the Modeling tab, select New Column.

4. In the formula bar that appears at the top of the screen, you can create your calculated column.

example code

BrandTotal = CALCULATE(
COUNT('Cars'[ModelTotal]),
ALLEXCEPT('Cars', 'Cars'[Brand])
)

In this example, CALCULATE is used to modify the context under which the count of the ModelTotal column is evaluated. The ALLEXCEPT function is used to remove all context filters in the table except for filters that have been applied to the Brand column.

then create your expected column. 

'Cars'[2dooravailable] & "/" & 'Cars'[BrandTotal]

hope that helps.

[descrt]

I think that a COUNT was used instead of a SUM, so I changed it to this instead but

BrandTotal = CALCULATE(
SUM (CarBrands[ModelTotal]),
ALLEXCEPT('CarBrands', 'CarBrands'[Brand])
)

I have to use the average in this case to get the number I want. Is it bad form to use a mixture of Sum and Average in a table?

Additionally, when I add in the concatenated text, the table splits into more rows because it treats the newly created text as a separate group as shown below. Is there a way to prevent this then?

I have two follow up questions 

a. is it bad form to