March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Solved! Go to 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
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!
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.
Can you please be more elaborate with sample data of I/p and o/p
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.
FY | Site | Value |
2019-20 | Site 1 | 30 |
2019-20 | Site 2 | 40 |
2019-20 | Site 1 % | 20% |
2019-20 | Site 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
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.
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
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
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!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |