Need help making a Filter for a Matrix after Unpivoting?

[derekli1700]

Hi, I'm trying to make a filter for my matrix table but i'm running into a problem with duplicate values when unpivoting / unsure if there's a easier way without unpivoting

My (simplified) data Test File.xlsx is just the:

1. Store's monthly volume

2. Product 1/2/3 or 4's contribution to said volume

3. Another column which value has to be averaged: 

This is the correct result after putting the data into a matrix table:

However I wanted to have PRODUCT 1/2/3/4 in a filter that could filter how much of the volume is attributed to that specific product in that table however to do this, i tried unpivoting which creates duplicate values seen below - which skews the data.

And if i try to remove duplicate values, it only puts PRODUCT 1 as the only filter choice rather than all 4 products in the table.

I was wondering if there's an easier way to do this and create a filter for PRODUCTS 1/2/3/4? Thanks

[v-karpurapud · Accepted solution]

Hi @derekli1700 
Thank you for reaching out to the Microsoft Community Forum.

I have attached the PBIX file. Could you please check if it meets your expectations? If not, could you share the details? Please let me know.

If I answered your question, kindly mark it as "Accepted Solution" so that it will help others find it easily.

Thank you.

[derekli1700]

Hi - this works thanks. Can you tell me how you did it for my future reference? Thanks

[Cookistador]

Hello @derekli1700 

Is it what you need?

If it is the case, this is the transformation I did in Power Query:

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\OneDrive\Documents\Power BI Report.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Period", type date}, {"Averaged value", type number}, {"Vol (L)", Int64.Type}, {"PRODUCT 1", Int64.Type}, {"PRODUCT 2", Int64.Type}, {"PRODUCT 3", Int64.Type}, {"PRODUCT 4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Period", "Averaged value", "Vol (L)"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Period", Order.Ascending}})
in
    #"Sorted Rows"

If you prefer to do it manually,
Select the product columns PRODUCT1,PRODUCT2,PRODUCT3,PRODUCT4 and select Unpivot columns

27 values are present for january 24 (in your excel sheet) and I got 27 lines in Power BI

Do not hesistate to ask if something is missing, or if it is not what you are trying to achieve

[derekli1700]

Hi - i typed that into advanced editor/did it manually and duplicate values still show. Going back to looking at the excel sheet - do you think there's an easier way to have a PRODUCT slicer visual for the matrix table i posted? I just want a slicer visual for PRODUCT that can interact with the table but i dont know if it can be done with the way my excel table is set up, if that makes sense