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  
X                           0                   0                          0                  0         
Y                           0                   0                          0                  0         
Z                      0.9920229         0.99202292         10.00%         99%

But when I make table in Desktop power bi company Z values should convert into percentage.
Can any one please help me how can I make it         

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.

     

4 Replies

  • 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.