Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How does one create a calulated column for the median value of a row with categories?

Hello,

 

 I'm having some issues with a custom column I just created. I have a table of the following (including the calculated column)

 

Risk Score   Category   Median Score

35                Apples       #ERROR

55                Apples       #ERROR

30                Grapes       #ERROR

40                Grapes       #ERROR

70                Grapes       #ERROR

 

I can't seem to get the Median Score to populate using the following, maybe because I'm having some challenges getting the proper expression. Do I use values, filter or what?

 

Median Score = CALCULATE(MEDIANX('Table 1','Table 1'[Risk Score]),ALLSELECTED('Table 1'))

 

11 REPLIES 11
Anonymous
Not applicable

To create a calculated column, you need to do the following:

  1. In the Fields pane, select the table you want to create a calculated column in. It is important to select the right table because if you choose wrong, you will have to delete and recreate the column in the right table.
  2. Click Modeling, Calculations, New Column.

Alternatively, you can right-click on the table and select New column.

The formula bar will then appear and you will need to specify your calculated column formula:

Because the formula is going to be evaluated row by row, you can reference other columns directly. For example, you can write the following formula:

Margin Column = Sales[Revenue] - Sales[Cost]

Once you write the formula and hit Enter, you can go to the Data view and see the new column:

Note how each row displays a different value because the formula is evaluated for each row separately.

You can also see the new column in the Fields list:

Although we have successfully computed the dollar value of margin, we cannot compute the total margin as a percentage of revenue correctly. The following calculated column formula does not provide the correct results:

Margin % Column = Sales[Margin Column] / Sales[Revenue]

To verify this, we can create a table visual with Revenue, Margin Column, and Margin % Column:

The correct Margin % at total level would be 560 / 1,750 = 32%. To understand what 104.67% means, it is useful to know that when you use a column field in a visual, an implicit measure is created, where the column values are aggregated using default summarization. To see what the default summarization for a column is, you can select the column in the Fields pane and see Modeling, Properties, Default Summarization:

It is worth noting that every column in a data model has a default summarization. While in this case, it makes no sense to add percentages by using the Sum default summarization, this is what Power BI chose by default. We can either change the default summarization or change the summarization method for a particular visual by right-clicking on the field in the field well of the visual:

At this stage, we may be inclined to use the Average summarization instead of Sum. Even if we choose Average, the values will still be incorrect:

This is because Power BI now takes an arithmetic average of the column values.

I hope this helps!

Regards,

Lewis

developer

apps4rent | o365cloudexperts

dax
Community Support
Community Support

Hi @Anonymous , 

You could try to use below M code to achieve median value 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZV0lFKLCjISS1WitWJVjJF4xsbAPnpRYkFEK4JKtcciRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Risk Score" = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Risk Score", Int64.Type}, {"Category", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"media", each List.Median([Risk Score]), type number}, {"all", each _, type table [Risk Score=number, Category=text]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Risk Score"}, {"Risk Score"})
in
    #"Expanded all"

 Or you could below measure to get this result

Measure 3 = CALCULATE(MEDIAN('Table (3)'[Risk Score]), ALLEXCEPT('Table (3)','Table (3)'[Category]))

 Please make sure the risk score is number type. And you also could to check the detail error information.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hmm, I don't need a measure. I need to add a new column with this...

 

CALCULATE (MEDIAN('ServiceNow Incident Data'[risk_score]), ALLEXCEPT ('ServiceNow Incident Data', 'ServiceNow Incident Data'[category]))

 

I get token literal expected

 

Should I be typing this a different way? 

 

I have tons of transforms already on this data but the category and risk_score columns are still correct.

 

 

dax
Community Support
Community Support

Hi @Anonymous , 

If you want to use M code to achieve this goal, you could refer to my first reply, which use M code. If you want to create calculated column , I think you could change expression like below

CALCULATE (MEDIAN('ServiceNow Incident Data'[risk_score]), filter('ServiceNow Incident Data', 'ServiceNow Incident Data'[category]=earlier('ServiceNow Incident Data'[category])))

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

I simply cannot add a custom colum in query mode. I need to do this but I'm confused about the token error below

 

Screen Shot 2020-02-18 at 3.26.54 PM.png

dax
Community Support
Community Support

Hi @Anonymous , 

In above replies, I provide three methods to get Median. You could try it again to see whether it works or not

1. M code

You could click Edit Queries, then chnage M code, group category and aggregate value(you could click the applied steps to see detailed expression and step).

587.PNG

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZV0lFKLCjISS1WitWJVjJF4xsbAPnpRYkFEK4JKtcciRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Risk Score" = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Risk Score", Int64.Type}, {"Category", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"media", each List.Median([Risk Score]), type number}, {"all", each _, type table [Risk Score=number, Category=text]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Risk Score"}, {"Risk Score"})
in
    #"Expanded all"

 

 2. calculated column

This is not in Power Query, it is in design pane. You could add it like below

588.PNG

3. measure

This is similar to calculated column, you could right click table to create measure. Then you could get below result

589.PNG

If this is not what you want, please correct me and inform me more detailed information (such as your sample data and expected output).

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

I would have thought that someone could simply provide a more straightforward answer to the query I posted and how to correct it. I need my table to update with the values out of that and then need to separately use them elsewhere for which a measure will not work. I can't post the data of table, that would not be a viable option. I cannot do an advanced edit of the query as there are plenty of other actions being performed prior. I need to perform what I shared in that window and how to correct the syntax for it for that window only.

 

Can you confirm you are in PowerBi (data view or report view) rather than in Power Query editor?

Anonymous
Not applicable

Hi @HotChilli I was currently in Power Query editor, attempting to create a custom column from the output of this.

Anonymous
Not applicable

Thank you Zoe!

 

I believe that's what I'm missing, ALLEXCEPT should perform median on risk score within category. Thank you!

HotChilli
Super User
Super User

Are you just trying to get the median but you get the "Expressions that yield variant data-type cannot be used.." error?

 

If so, just use a simple

column = MEDIAN(Table 1[Risk Score])

instead of the formula shown BUT make sure to change the data type of the [Risk Score] column to decimal.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors