Solved: Re: Two important questions about analyze in excel

Anonymous · ‎11-02-2021

Hi Everyone,

I have two questions about analyze in Excel

1. Why does months get broken up like this in the pivot table? also how can I fix it? becouse in the created month column in data they all come in this format Oct 2021.

2: When I try to calculate how many tickets I got for the month by putting ticket type in value I get this error message "The Field you are moving cannot be placed in that area of the report). How do I resolve this issue becouse I want to see how many help desk tickets were summited in Jul 2021. Right in the data column in power bi the column ticket type only has to values Service request and Incident.

ImkeF · ‎11-03-2021

Hi @Anonymous ,
hm, that looks strange.

Do the fields in your pivot table all come from one table or are there multiple tables involved. If so: How are they related?
Also, please share the formula of your measure.
Finally please share a snapshot of the pivot table layout

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

ImkeF · ‎11-02-2021

Hi @Anonymous ,
for 2) You have to create an explicit measure in Power BI to use in the value-section of an Excel-Pivot table. Implicit measures will not work.
for 1) I don't understand the problem. The formats you've described (Oct 2021) and showed (Jan 2019) are identical. Once you've dragged a valid field (see 2) into the values-section of the pivot table, only those dates will show that have values in them.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous · ‎11-03-2021

Hi @ImkeF

So right now my pivot table looks like this below. There are 2,460 row for Jan 2019. All I want is just one row for Jan 2019..etc. Like the old version I had of this report below: I dont know if I need to go back into power bi and do somthing in data.

Old Version:

ImkeF · ‎11-03-2021

Hi @Anonymous ,
hm, that looks strange.

Do the fields in your pivot table all come from one table or are there multiple tables involved. If so: How are they related?
Also, please share the formula of your measure.
Finally please share a snapshot of the pivot table layout

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous · ‎11-04-2021

Hi @ImkeF

I was able to solve this issue be deleting the old column in data and putting this one in place.

I did have one interesting question for you. I am using Matrix in Visualization report. How do I reoder the created months row below so that is shows 2019 to 2021 in order ?

This is the Order I am looking to replicate: