I’m having a hard time describing the problem, but I think if you look at the Example Table Goals and then the model you will see the issue I’m facing.
Context:
A table showing the $ amount across multiple levels of categories A, B, and C
There are 3 measures: Amount This Year, Amount Last Year, and $ Diff vs Last Year
The table includes the Month in the columns field
Issue with the current table:
I don’t want to see the Last Year amount and $ Difference for each month. Only This Year’s amount for each month.
BUT I want to see This Year’s and Last Year’s total for each category, as well as the total $ Difference between both years.
The table I’m trying to make:
A separate column for each month for This Year.
A column for the Total for This Year.
A column for the Total for Last Year.
A column for the $ Difference between This Year and Last Year
Thanks for posting your question @BillK To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
Well @BillK, it is possible, but not as simple as you might want.
You will need to create a measure to define the table, as well as a measure to determine what measures are being called.
I was able to find this solution in the Community.PowerBi forum:
the second response in the post is the solution, with a link to a PowerBi file you can download.
However, I am going to suggest that you also ask yourself if including all of this info in the same table is actually necessary. One of the hardest things that any PowerBi developer has (in my opinion) is overcoming the ‘think like Excel’ logic. You could have Year to Date logic in a separate visual from monthly logic.
If you really want to output your data in an Excel-like manner, then consider developing IN Excel. PowerBI is native in Excel (since 2016 I think), and you can add in elements as far back as 2010.
Also thanks for asking the deeper questions on if this should be done in Power BI or in Excel. It is a question I keep bumping into. Being new to Power BI, I keep trying to mimic what I did in excel. Then as I discover more of the features and capabilities of Power BI, I realize it opens up possibilities to do things differently. The related challenge is changing the routine of the report users. They are used to seeing the info in the established format, and can struggle with changing their mental models to adapt to the new way. But at least Power BI offers so much capability that is worth the effort to figure out new ways to do things.
I also struggle with my users, who are accustomed to certain ways of seeing things in Excel and occasionally still think it should work the same way in PowerBI.
One solution I had with one of my early reports that was trying to show monthly data and year to date data in the same area - was to create two different tables, and sort the data in both tables by an overall year-to-date ranking (I created a rank measure that was in both tables).
The monthly table was rank, item code, item description, monthly columns
I then copied that and removed the monthly calculation to add in Year to Date, and Year Over Year data
To maintain the sort of both items, I put a box over the two visuals (transparent, so my users could not click on them). I have not had issues with that particular report since then.
Good luck - it’s a fun, but challenging journey as you progress from Excel developer to PowerBI developer.
