Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
Need Custom column or calculated column, where if Unit = #, then Actual KPI needs to come without any calculation and if Unit = % then kpi needs to come as % format.
Note: both needs to come same column.(Need Exactly as Result column)
Unit | KPI | Result |
# | 83.7 | 83.7 |
# | 92.1 | 92.1 |
# | 67 | 67 |
% | 96.3 | 96.30% |
% | 94 | 94% |
% | 88.4 | 88.40% |
% | 92 | 92% |
Solved! Go to Solution.
@Anonymous ,
You will not be able to have two different formats in the same column. If you only need this for display purposes, you can make this column text and it will do this. You could then convert the value from text by using dax as needed.
In PQ, here is the column:
if [Unit] = "%" then Text.Combine({Text.From([KPI]),"%"}) else Text.From([KPI])
Here is my table code in PQ.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlbSUbIw1jOHUbE6EDFLIz1DGAUTMzOHECC+KkjSTM8YShmoIkRNwARCwMJCzwRKISszAhNAgVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unit = _t, KPI = _t, Result = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit", type text}, {"KPI", type number}, {"Result", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "myResult", each if [Unit] = "%" then Text.Combine({Text.From([KPI]),"%"}) else Text.From([KPI])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"myResult", type text}})
in
#"Changed Type1"
Paste this into a blank query to get the table.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
@Anonymous ,
You will not be able to have two different formats in the same column. If you only need this for display purposes, you can make this column text and it will do this. You could then convert the value from text by using dax as needed.
In PQ, here is the column:
if [Unit] = "%" then Text.Combine({Text.From([KPI]),"%"}) else Text.From([KPI])
Here is my table code in PQ.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlbSUbIw1jOHUbE6EDFLIz1DGAUTMzOHECC+KkjSTM8YShmoIkRNwARCwMJCzwRKISszAhNAgVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unit = _t, KPI = _t, Result = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit", type text}, {"KPI", type number}, {"Result", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "myResult", each if [Unit] = "%" then Text.Combine({Text.From([KPI]),"%"}) else Text.From([KPI])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"myResult", type text}})
in
#"Changed Type1"
Paste this into a blank query to get the table.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!