Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need the formula to extract the first values in the different rows shown in bold in this matrix, to obtain a column only with this values in bold and not the rest.
Column [Levels]
Row [Months]
Values [Prices]
Months | 1 | 2 | 3 | 4 | 5 | 6 |
ene-19 | 12.000 | 11.000 | 8.000 | 7.000 | 6.000 | 5.000 |
feb-19 | 15.000 | 14.000 | 13.000 | 12.000 | 11.000 | |
mar-19 | 16.000 | 15.000 | 14.000 | 13.000 | 12.000 | |
abr-19 | 17.000 | 16.000 | 15.000 | 14.000 | 13.000 | |
may-19 | 18.000 | 16.000 | 15.000 | 14.000 | ||
jun-19 | 19.000 | 18.000 |
This is done more easily in the query editor. To see how it works with your example data, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs1L1TW0VNJRUoAAQyM9AwMDJL4hKh8ILDBEzDFEzDBETMEisTrRSmmpSVArkawxRbPWBI1vjMbH7kyQ8bmJRZjGo7mHTOtAxicmYTEeLQDItA7i+kok45GUWJBlBcjIrNI8NCPRjLZE0wqxKjYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Months = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t, #"5" = _t, #"6" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Months", type text}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}, {"5", Int64.Type}, {"6", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Months"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Months"}, {{"AllRows", each _, type table [Months=text, Attribute=text, Value=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "TopRow", each Table.FirstN([AllRows],1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
#"Expanded TopRow" = Table.ExpandTableColumn(#"Removed Columns", "TopRow", {"Attribute", "Value"}, {"Attribute", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded TopRow",{{"Attribute", Int64.Type}, {"Value", Int64.Type}})
in
#"Changed Type1"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
So for each month you want the value that was recorded at the lowest level that has data. Write your DAX accordingly.
Something like
measure = FISRTNONBLANKVALUE([levels],sum([prices]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |