- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Grouping and formatting issues table
Hi,
Have noticed couple of areas I'd like to check if I am doing things right: I have two separate tables - one is with actual financial data and the other has our budget. We can and have been able to break this down by month, quarter, year etc.
However I have noticed something. In the report, when selecting a particular month no actuals appear for that category (which is correct) but rather than showing a blank value in the table, nothing for that category appears either for actuals or budget.
It should look like this:
Category | Actuals | Budget |
IT | £0 | £2.3K |
Staff costs | £22.3M | £26.4M |
But instead has:
Category | Actuals | Budget |
Staff costs | £22.3M | £26.4M |
Which is throwing off figures - how do I get this otherwise blank value to appear?
Secondly, the data is comnprised of 8 categories (they are the same categories across the two excel datasets). I'd like to display a certain 4 (Staff Costs, Travel, Marketing, IT) and then group the rest as 'others' and do so such that it works across both excel datasets on the report.
How is it best to do this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @tobiasmcbride ,
1.To make blank value appear:
So the acutals and budget are from 2 tables right? If so ,create a dim table,then create a relationship between dim and actuals, dim and budget.
2.About group:
In the dim table,right click on the column of category,choosing "new group",then choose each row you dont need to be grouped and others which are needed to be grouped in the other ,and you will see:
Kelly
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
If I do that, the slicers/filters do not work for the budget. Also blank categories appear and no IT Expenses appear in the month view when I know there is a budget entry for that even though no actuals view.
Not sure what I am doing wrong?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @tobiasmcbride ,
Can you show me your .pbix file if it's not confidential?
Kelly
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I doubt it is getting data 0 for IT. Just allow no dataAppreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin values and check.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have selected that option and still does not appear.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
10-25-2023 08:07 AM | |||
02-02-2024 07:14 PM | |||
05-13-2024 10:56 AM | |||
05-17-2023 03:45 AM | |||
05-15-2024 05:38 AM |
User | Count |
---|---|
141 | |
115 | |
84 | |
63 | |
48 |