Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
PrashantA
Helper I
Helper I

Help on two values (Value and %) in one column to be displayed in Power Bi visual

Hi,

 

Seek your help on how to display same column data where both % and value existed after applying unpivot option. In visual a person can only select either value format or % format.

 

E.g. Site 1 and Site 2 have both expenses in Indian rupees and also expense % of Sales.

 

Please help.

 

Thanks and Regards,

PrashantA

1 ACCEPTED SOLUTION

Oh! now I understand better what you need.

After you have the data in the query editor, this can be solved in 3 steps:

1) Identify the rows with values or percent.

 

Go to Add Column -> Conditional Column

Conditional Column.PNG

 

Enter the following parameters:
Conditional Column Parameters.PNG

 

2) Remove the extra text from the Expend Head column:

 

Select the column Expend Head , go to Transform -> Replace Values, enter the following and click OK.

Replace values.PNG

 

3) Create one column for values and another for % of Sales

  • Select the Titles column (you created this one on step 1) -> Go to Transform -> Pivot Column
  • Then on the section Values Column, select Value and click OK

 

Optionally: Change the data types and names accordingly to get this result:
Final Result.PNG

 

Hope this helps!
Orlando Mezquita

@PrashantA If this solved your problem, please mark this reply as a solution. Thanks!

View solution in original post

9 REPLIES 9
orlandom
Advocate I
Advocate I

Hi @PrashantA ,

If you are using either a pie chart or a donut chart you can do as follows:
1) Select the visual

2) Go to Format (Paint roller) -> Detail Labels -> Label Style
3) Select the type of label: Value, Value and %, just %.

There's no need to do data transformations for this as long as the % is calculated from the total.

pranit828
Community Champion
Community Champion

Can you please be more elaborate with sample data of I/p and o/p





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Hi Pranit,

 

Here is the small example: For Site 1 and 2, both value and % are reflected in Value column after applying unpivot on selected columns in Power Bi...Now if I make a duplicate of Value column and name it as "Value %" then in FY pie chart I can either add value or value % and hence checking if there is way to separate two different formats (% and value) to reflect both value and % too.

 

FYSiteValue
2019-20Site 130
2019-20Site 240
2019-20Site 1 %20%
2019-20Site 2 %40%

 

I'm guessing there's more data than just this?

 

If not, why not skip the Unpivot step, and you'll have FY, Site, #, % as seperate columns, and then you can only use the column you want?

 

FOrrest




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Hi,

Correct the data is huge....30 locations and 20 expense type. The example shared below is just for two locations and one expense head.

Hi,

Share some more data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

Below is the sample data and expected output is to have Fiscal year wise visual where Site wise exps to be displayed in one visual and expense % of Sales in another visual however both the visuals should change after applying fiscal year, site, expense head slicer.

FYSiteExpense HeadValue
2019-20Site 1Admin30
2019-20Site 2Logistics40
2019-20Site 1Admin % of Sales20%
2019-20Site 2Logistics % of Sales40%
2018-19Site 3Travel10
2018-19Site 3Travel % of Sales35%
2018-19Site 4Salary70
2018-19Site 4Salary % of Sales25%

Thanks and Regards,

Prashant A

Oh! now I understand better what you need.

After you have the data in the query editor, this can be solved in 3 steps:

1) Identify the rows with values or percent.

 

Go to Add Column -> Conditional Column

Conditional Column.PNG

 

Enter the following parameters:
Conditional Column Parameters.PNG

 

2) Remove the extra text from the Expend Head column:

 

Select the column Expend Head , go to Transform -> Replace Values, enter the following and click OK.

Replace values.PNG

 

3) Create one column for values and another for % of Sales

  • Select the Titles column (you created this one on step 1) -> Go to Transform -> Pivot Column
  • Then on the section Values Column, select Value and click OK

 

Optionally: Change the data types and names accordingly to get this result:
Final Result.PNG

 

Hope this helps!
Orlando Mezquita

@PrashantA If this solved your problem, please mark this reply as a solution. Thanks!

Hi,

This M code transforms your data in a format that would allow you to build your desired visual

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1MlDSUQrOLElVMAQyHFNyM/OAtLGBUqwOhgojIMMnPz2zuCQzuRjINsGqCm6OgqpCfppCcGJOKkixkYEqQTNRdZggdFjoGlrCdBgDGSFFiWWpOUCGoQF+JagmGptiNdEExEjMSSyqBDLMsZqIUILmK5CJsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FY = _t, Site = _t, #"Expense Head" = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FY", type text}, {"Site", type text}, {"Expense Head", type text}, {"Value", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Site", Order.Ascending}, {"Expense Head", Order.Ascending}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Expense Head", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Expense Head.1", "Expense Head.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Expense Head.1", type text}, {"Expense Head.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,"Amount",Replacer.ReplaceValue,{"Expense Head.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," of Sales","",Replacer.ReplaceText,{"Expense Head.2"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[#"Expense Head.2"]), "Expense Head.2", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"%", Percentage.Type}})
in
    #"Changed Type2"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.