This is my first post so I apologize if I've missed anything obvious.
My issue has to do with the Matrix tables. When I am viewing my table in Power BI (either desktop or published), the output is absolutely perfect. The essential field here is a year-by-year dollar amount for each individual.
Here's a snippet: Link
The use case here is a mail merge, so the one-row-per-person must be maintained. As many of us know, when we export a matrix table, the data is not aggregated and needs to be re-pivoted in Excel to produce a similar Matrix. At my organization, each chapter (there are a lot) uses this report to produce their own mailing lists and having them repivot the tables is going to inevitably introduce expensive mistakes.
My question: Is there *anything* I can do to export a matrix visual precisely as it is seen in the report?
I've googled it endlessly and haven't stumbled upon any hard solutions as of yet. I'm looking into producing a custom visual with R script, but before I start on that I am ending my due diligence with a post to these forums. I'm open to any solution, however difficult or unconventional. I am trying to get our organization to use Power BI as its primary reporting outlet and this is a MAJOR roadblock - it's absolutely critical for us.
Hi @rschaeffer ,
In Power BI, I'm afraid that Export Data feature is used for exporting data, without keeping visual format in .CSV file currently. You can take a look at this article: Export data from Power BI visualizations.
To work around the issue temporarily, you may could create a PowerPivot table in Excel. Or you can use PowerBI Tiles add-in in the Excel to get matrix visual. See: Integrating Power BI Tiles into Office documents.
Besides, you also coulf vote and comment on this similar idea Allow Table and Matrix Data Exports to Match PowerBI Visual.
The problem is more that the organization is very Excel dependent, and oftentimes they need their own working copy of the document, as opposed to just viewing the content.
My main column that I need is a year-by-year financial statistic; I was considering just making a calculated column for each year.
e.g., "2019 Revenue", "2018 Revenue", and so on and adding it as an additional report page. The main issue here is that if they use the slicers to filter the report, those columns either won't change values (if it's a column) or they will zero out (if they are a measure). Is there any way to conditionally display a column/measure to replicate the output of a matrix visual using this strategy?
Has anyone else in the community come up with similar workaround?
@rschaeffer Did you ever find a workaround to this? I am in the middle of trying to migrate a small group of users off Tableau and on to Power BI. But they frequently send filtered views of their reports to outside suppliers, etc., from Tableau. Asking them to create something in Power BI, then re-create that same thing as a pivot in Excel is just not going to happen.
Not really. In my case, the Matrix report was a year by year financial report, and I made a calculated field for each year and just used an ordinary table. There are a lot of caveats to this, but it does export correctly.
Is this why my export of matrix table does not match the counts and sums that appear in power bi desktop and online? It is only 8000 rows, but filters do not appear to be working because the results in excel export are higher counts and sums than appear in power bi.