Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Unpivoting Data Problem

I have a data set with columns: Income Statement Type, Product Type, "January 2017", "Feb 2017",.... "March 2019" .  This lets me see Sales, COGS, and Op Ex by product for these time periods.  

 

The data set is built in excel via OLAP connection to a cube. I then connect Powerbi to that excel file. When i import in the data, I have a column for every month's sales. This is hard for me to work with so I want to unpivot the data.

 

I select all the columns except for the Month&Year combination columns and then say "unpivot all others" when I do this I then get different values than what is accurate. For example in my excel sheet, total sales could be 1Million but after I unpivot the total is now 400k. I have no idea why this may be occuring but I am looking for input.

 

I unpivoted the data in excel and it works perfect so the error is on the PBI side. Unpivoting in excel is not an option going forward due to file size

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous not sure why that would be the case, just to clarify, 1M sales is for all the months, correct?

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous 

    Could you show some screenshots about it. and if you have test the total result is correct?

    If you could share some sample data and your expected output for us to have a test, 

    You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

     

     

    Best Regards,

    Lin

     

  • Nishantjain's avatar
    Nishantjain
    Continued Contributor
    7 years ago

    Anonymous 

     

    Have you tried to narrow your search and identify where the issue is? I would suggest you select a sample month and product and see if the unpivot reconcile to the excel spreadsheet.

     

    Thanks

    Nishant