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

[PrashantA]

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

[orlandom]

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]

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

[PrashantA]

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.

FY	Site	Value
2019-20	Site 1	30
2019-20	Site 2	40
2019-20	Site 1 %	20%
2019-20	Site 2 %	40%

[fhill]

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

[PrashantA]

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.

[Ashish_Mathur]

Hi,

Share some more data and show the expected result.

[PrashantA]

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.

FY	Site	Expense Head	Value
2019-20	Site 1	Admin	30
2019-20	Site 2	Logistics	40
2019-20	Site 1	Admin % of Sales	20%
2019-20	Site 2	Logistics % of Sales	40%
2018-19	Site 3	Travel	10
2018-19	Site 3	Travel % of Sales	35%
2018-19	Site 4	Salary	70
2018-19	Site 4	Salary % of Sales	25%

Thanks and Regards,

Prashant A

[orlandom · 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

Enter the following parameters:

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.

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:

Hope this helps!
Orlando Mezquita

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

[Ashish_Mathur]

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.