Mimic Excel Pivot table in PowerBI

[aksharacoomar]

Hi all,

I am building an Actual vs. Plan report on PowerBI which we currently have on Excel. Although I have most of the things I need in the right form, I do have some trouble areas pertaining the weeks, my report must include the following date hierarchy - Year, Quarter, Month, Month Name + Week # and Date. I do have a calendar table built in using DAX. 

My trouble areas are below: 

1. 454 Calendar. My company uses a 454 Calendar and I need to break my data down by month name and week # (eg: Oct Week2). How can apply a 454 calendar to my data? Below is a snip of my Excel report the weeks need to be broken out that way. 

2. The subtotals do not show the Month name - How can I add that? When the month is expanded, the name of the month is visible but when I contract it, I see all the totals but I do not see the name of the month. 

Thank you in advance! 

[v-rzhou-msft]

Hi @aksharacoomar 

I build two kinds of data models and both of them works well.

Data model1:

data model2:

Result:

Could you show me your data model and tell me more details about how did you build you matrix visual?

Or you can share your pbix with me by your Onedrive for Business.

Best Regards,

Rico Zhou

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

[edhans]

Are you sure? 

Get rid of that black backround and see if your data looks ok. It might just be the title is black and the font is as well. When I collapse a subcategory in a matrix, it shows the totals and the name of that category (State in this example)

[lbendlin]

Either simplify the ask, or continue to use Excel.