Use Column headers as slicer

[Anonymous]

Hello to all,

I am new here and was wondering if someone could help me.

I have a table in Excel, that looks like the screenshot below. The table has more than 4000 rows.

I would like to make a slicer in Power bi to be able to select the month, the sum (Gesamtergebnis), FBEREICH and var/fix  but with this structure it does not work. So I tried the funktion unpivot. I selected the columns which I want to have in a slicer and click on unpivot selceted columns. It looks good, the values are wrong. I do not know why.

I also tried it with a smaller test table ( only 4 rows) with the same structure and it works. The values are correct.

Maybe someone could help me or give me another approach?

Thank you! 

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

I did a test, you can use unpivot column only for the month column in powrbi query, and then filter out the data corresponding to the month.

If the problem is still not resolved, can you provide a test data model (delete sensitive information) so that I can answer it for you as soon as possible. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-henryk-mstf,

thank you for your answer. I think I found the issue.

I had some errors and no data in table, after replacing the errors with a 0 I got the correct values.

Maybe you have some advice for me, how to replace the errors and cells without data, to get a correct unpivot table.

Thank you and best regards,

Albert

[ERD]

Hi @Anonymous ,

You need to choose all month-related columns (JAN - DEZ) and then Unpivot them. Your sum (Gesamtergebnis) will be counted in the report (by a measure or implicitly). 

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Anonymous]

Hi @ERD.

unfortunately it shows me still the wrong values.  There are no formulas in the table, so this can also be excepted.