Forum Discussion

infantpowerbi's avatar
infantpowerbi
Helper II
6 years ago
Solved

Convert the Value into Percentage based on condition

Dear All, I am infant topower bi and facing some challenges due to being new to power bi I am holding data in below format Company        Q2 FY 2018  Q3 FY 2018         Q4 FY 2018  Q1 FY 2019 ...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi infantpowerbi ,

    Can you use calculated column? If yes,  you can try the formula below. If not, I think you need to convert the Value column to percentage entirely.

     

    Z Percentage = IF('Table'[Company] = "Z",FORMAT('Table'[Value],"00.00%"))

    Best Regards,

    Xue Ding

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

     

v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

Hi infantpowerbi ,

If you just want to convert the values of Z to percentage and others show decimal number, you need to unpivot the table in Query Editor.

1. Unpivot columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUTJAwbE60UqRWEWjQCw9S0sjAyMjS2S2EZhjCBVSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t, #"Q2 FY 2018" = _t, #"Q3 FY 2018" = _t, #"Q4 FY 2018" = _t, #"Q1 FY 2019" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Q2 FY 2018", type number}, {"Q3 FY 2018", type number}, {"Q4 FY 2018", type number}, {"Q1 FY 2019", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

2. Apply and then create a measure

Measure = CALCULATE(FORMAT(SUM('Table'[Value]),"00.00%"),FILTER('Table','Table'[Company] = "Z"))

If you want to all the values shown as percentage, you could click % in Modeling tab.

Best Regards,

Xue Ding

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

 

infantpowerbi's avatar
infantpowerbi
Helper II
6 years ago
Hi,
My client denied to use measure,
So can we convert value column into percentage where company is equal to z
  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi infantpowerbi ,

    Can you use calculated column? If yes,  you can try the formula below. If not, I think you need to convert the Value column to percentage entirely.

     

    Z Percentage = IF('Table'[Company] = "Z",FORMAT('Table'[Value],"00.00%"))

    Best Regards,

    Xue Ding

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

     

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi infantpowerbi ,

    Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.

     

    Best Regards,

    Xue Ding

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