Matrix is Grouping Rows - Results Don't Match Tutorial Video

[Anonymous]

Hello

I've only started trying to learn Power BI recently so this is going to be a very noob question but hopefully one that has a simple answer. I'm watching the video in this link: https://docs.microsoft.com/en-gb/learn/modules/model-data-power-bi/4b-create-calculated-measures and also using his data set. He has created what I believe to be a 'matrix' visual with 'MonthName' in the Rows section and 'Year' in the Column section. When he adds the 'Revenue' field to the report his result set is displayed with each month displayed vertically:

Tutorial

However, when I do the same it groups all my months and years together to just give an overall total and I lose the separate months and years. The matrix looks correct before I add the 'Revenue' column.

Is this a simple/easy fix and can somebody advise what I might be missing? Expand, Collapse and Group options are all greyed out when I right-click on the matrix.

[Anonymous]

Hi David

Thanks, that fixed it! I assumed the problem was with the Date table rather than the Sales table so thanks for that.

I received an error though but I think that's more to do with locale issues - I think the data may be in US format and I'm in the UK but it seems to have sorted itself out:

DataFormat.Error: We couldn't parse the input provided as a Date value.

Details:

    1/15/1999

[dedelman_clng · Accepted solution]

Hi @Anonymous - the "Date" field on the sales table is stored as text not as a date.

Change it to a date, either in the query (Transform Data) or on the column itself (highlight column in the Fields pane, go to Column Tools, change "Text" to "Date" under Data Type on the far left.

Hope this helps

David

[dedelman_clng]

Hi @Anonymous  - check your date/calendar table and how it was created. This behavior will happen if your date table does not contain the dates on your Sales table.

Hope this helps,

David

[Anonymous]

Hi David

Thanks for your prompt response. It isn't 'my' data as such, it's provided by Microsoft as part of the lab I was following so unfortunately I don't know anything about it or how it was created.

Could it have something to do with the fact that the Year column is classed as a number? And not a date? Therefore Power BI cannot associate it with the dates in the Sales table?

I created a copy of the column and changed it from Whole Number to Date but then it turns 1999 into 21/06/1905, which isn't correct either...

[dedelman_clng]

@Anonymous - can you share the pbix you are using? Post a link to google drive/dropbox/onedrive, etc.

[Anonymous]

Hi David

Please see attached: Link 

Thanks