Forum Discussion

adii's avatar
adii
Helper I
3 years ago
Solved

Problem with legend and measure in plot

Hello 

 

I have a little problem with line chart and measure. 

I have plot with measure (sum of values) on Y Y axis  and when I put on legend name of country I see couple of line depends which country I chosen on slicer , so it's right . But when I want put on plot another measure (Average of values for all country ) I couldnt do that . 

so how solve that problem ? 

I see solve the problem . I can make a lot of separate measure( calculate ...., Filter =Spain... etc )  for each country but it will be tough, 

 

someone could help ? 

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi adii ,

     

    Based on your description, I have created a simple sample:

    Please try:

    First create a new table like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xDcAwCETRXahdGDBJ2iRjWOy/hk1ko0txzZO+oHeSKkKF7jmu5CXlCTlQ3jmxLboqZZSo5CdRcUpblTWUqFRQorK8bvvDE+WrGkpUepH7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Legend = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Legend", type text}, {"Value", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Legend"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each {_[Legend],"Average"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Legend", "Country"}})
in
    #"Renamed Columns"

    Then build relationship between the two tables:

    Create a slicer:

     

    Apply the measure to the line chart:

    Measure = 
var _a = ALLSELECTED('Table'[Country])
return 
IF(SELECTEDVALUE(Legend[Custom])="Average",CALCULATE(AVERAGE('Table'[Value]),FILTER(ALL('Table'),[Year]=SELECTEDVALUE('Table'[Year]))),SUM('Table'[Value]))

    Final output:

    Best Regards,

    Jianbo Li

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

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi adii ,

     

    Based on your description, I have created a simple sample:

    Please try:

    First create a new table like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xDcAwCETRXahdGDBJ2iRjWOy/hk1ko0txzZO+oHeSKkKF7jmu5CXlCTlQ3jmxLboqZZSo5CdRcUpblTWUqFRQorK8bvvDE+WrGkpUepH7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Legend = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Legend", type text}, {"Value", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Legend"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Custom", each {_[Legend],"Average"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Legend", "Country"}})
in
    #"Renamed Columns"

    Then build relationship between the two tables:

    Create a slicer:

     

    Apply the measure to the line chart:

    Measure = 
var _a = ALLSELECTED('Table'[Country])
return 
IF(SELECTEDVALUE(Legend[Custom])="Average",CALCULATE(AVERAGE('Table'[Value]),FILTER(ALL('Table'),[Year]=SELECTEDVALUE('Table'[Year]))),SUM('Table'[Value]))

    Final output:

    Best Regards,

    Jianbo Li

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