Slicer in columns

[CarlsBerg999]

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€

[v-eqin-msft]

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

[pranit828]

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.

https://www.youtube.com/watch?v=9Xv8COs59tc

[amitchandak · Accepted solution]

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