Forum Discussion
Helper VSlicer in columns
Hi,
I have an issue with Power Pivot and my current data. The idea is identical as with Power BI, so i posted it here. I have three columns: Sales 2019, Sales 2020 and Sales 2021. I want to sum these and present a pie chart with a drilldown to each of these.
I have no idea how to make a measure / calculated column for these, because the year label is in the column and not in the row. Any ideas?
| Customer | Year 2019 | Year 2020 | Year 2021 |
| Example 1 | 10 000€ | 100 000€ | 2 000€ |
| Example 2 | 100 000€ | 5 000€ | |
| Example 3 | 200 000€ |
CarlsBerg999 , Not very clear.
You can unpivot the data. And separate out year
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/Also, you can use more than one column in pie. Do not give legend in that case.
3 Replies
- amitchandak
Super User
CarlsBerg999 , Not very clear.
You can unpivot the data. And separate out year
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/Also, you can use more than one column in pie. Do not give legend in that case.
HI CarlsBerg999
I would unpivot which will bring year to row and then extract the last four characters to create a new column. RIGHT([Column],4)
Use the below link for unpivot.
Hi CarlsBerg999 ,
According to my understanding, you want to use Pie chart to drill down Sales based on Customer and Year , right?
For my test ,you could follow these steps :
1.In Query Editor ,Select the Customer column and select ‘unpivot other columns’ in Transform tabs
2.Ensure the data type of Value column is Whole Number .The whole applied steps as follows:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcq1IzC3ISVUwVNJRMjTQMTAweNS0BsxG4hjBmLE6CB1G6KpMEUwFFJXGYBGgMciqgUpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Year 2019" = _t, #"Year 2020" = _t, #"Year 2021" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Year 2019", type text}, {"Year 2020", type text}, {"Year 2021", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Year"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","€","",Replacer.ReplaceText,{"Value"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Value", Int64.Type}}) in #"Changed Type1"3.Create a Pie Chart and drag Year and Customer fields to Legend ,Value(default aggregation is Sum) to Values ,like this:
Is the result what you want? If you have any questions, please upload some data samples and expected output.
Please do mask sensitive data before uploading.
Best Regards,
Eyelyn Qin
