The Excel file described herein is avaialbe at (data is in Sheet1, PivotTable is in Sheet2):
The Power BI file described herein is available at:
I need to create a Matrix visualization in Power BI that replicates an Excel PivotTable. One of the columns in the Excel PivotTable is a calculated measure, and I was able to get it present just like I wanted it by adjusting its Value Field Settings. But that flexibility does not appear to be available for a Matrix in Power BI. I've been trying to reproduce it with a new measure, but I can't get the needed results.
To illustrate the objective I'm trying to achieve, here is the Excel Pivot Table (Sheet1 of the Excel file). It is based on a single stream of data that contains both balance and transaction information. (The data is organized that way for a very specific reason. In real life, it is part of a much larger data model.)
To help demonstate what the pivot table above is doing, I have expanded a few levels of rows selectively and left the rest collapsed. Each row presents the period indicated at the left-hand side, showing you the beginning balance for that period, all subsequent activity in the period, and finally the total (which is a proxy for the ending balance). Notice that if the row is the fiscal year, then even though the activity it dsplays spans the entire fiscal year, the only begining balance data that is relevant are the the begining balance data as of the first month in that fiscal year; all other "beginning balance" rows for months during the fiscal year are ignored on that row. So depending on whether you are looking at the beginning balance for a year, a quarter, or a particular month (or a particular account on a monthly basis, which is the lowest level of the row hierarchy), the report needs to compute beginning balance differently.
Here is a screen shot showing the strucutre of the single table containing all of the data used in that PivotTable (Sheet2 of the Excel file):
(FYI, The PivotTable filters out the "Balance ending" data rows, as these are superfluous and I prefer to have the ending balance be computed as a PivotTable column grand total.)
Because of the way this PivotTable is structured as described above, the values section of the PivotTable could not be populated directly with the amounts field in the underlying data. The first amount in the values section is actually a DAX measure rather than the raw numbers contained in the underlying data. The code for the measure is below, and as you can see, it allows the computation of beginning balances to be context sensitive:
Amount, rollable:=SUMX(FILTER('Table1', ('Table1'[Row type]="Balance beginning" || 'Table1'[Row type]="Balance ending" ) && 'Table1'[End of month of balance or flow]=MIN('Table1'[End of month of balance or flow])), 'Table1'[Amount]) +SUMX(FILTER('Table1', 'Table1'[Row type]<>"Balance beginning" && 'Table1'[Row type]<>"Balance ending"), 'Table1'[Amount])
The second amount in the values section is the same DAX measure again (renamed for presentation purposes), but this time presented as a percentage of the numbers contained in rows bearing the "Balance beginning" "Row Type" field:
This second field is intended to start at 100% of any beginning balance (whether beginning of year, quarter, or month), show the relative size of each of the subsequent flows in the period relative to the balance at the beginning of the period, and show the the computed grand column total (which is a proxy for the ending balance records in the data table) as a percentage of the beginning balance as well. As you can see, it does all but the last of these well. Not perfect, but fairly successful.
I need to recreate the above results as a Matrix in Power BI--prefereably solving for that one missing portion as well.
When I loaded the same data into Power BI and tried to recreate this report as a Matrix visualization, I was unable to find the same flexibility to present a second appearance of the measure as a % Of another column. If the ability to do that is there and I'm missing it, please let me know; it would certainly be the easiest solution to my problem.
As an alternative, I tried to create a second measure that does the job. But after a couple of days of trying, I'm unable to acheive the desired results. A snapshot of the Matrix so far is below, but as you can see, the second measure (the % one) isn't performing like it needs to in order to replicate the Excel PivotTable presentation:
Solved! Go to Solution.
Hi, parry2k. Thanks for your response.
First, I want to point out that for some reason the Excel data PivotTable was having trouble with a few of the 1/31/2017 data rows. I massaged the underlying data table a bit to fix it. I've updated the link to provide the updated Excel file.
Your solution provides the correct answer for the fiscal year rows, the fiscal quarter rows, and the individual month rows. However, it does not provide the right answer for the individual accounts. For instance, consider the month ending 3/31/2017. In the aggregate, Flow type C is (750), which is indeed (7.12)% of the 10,528 aggregate beginning balance for this month end, as can be seen in the Excel PivotTable--and your solution provides the same result. However, if you dig into the details for that month, you see that the entire (750) happens to come from Account #2, and as a percentage of that account's beginning balance, 1,030, this is a much more significant (72.82)% as the Excel PivotTable shows--but your solution is reporting the percentage for that account-specific row as being the same as for the all-accounts aggregate row, -7.12%. Can you refine your solution to fix this?
I appologize; I meant Flow type B, not Flow type C. I was trying to compare two rows from your snapshot to the two corresponding rows in the Excel PivotTable highlighted below:
Notice that column G of the Excel PivotTable table shows (7.12)% on the the aggreagate 3/31/2017 row, whereas it shows (72.82)% on the Account #2 row for that month. This is because even though the aggregate Flow type B for that month is comprised entirely of the (750) that Account #2 experienced, this (750) is a much larger percentage of Account #2's March beginning balance than it is of the March begginning balance for all accounts added together. But the snapshot you showed me presents both of these rows as being (7.12)%.
Thanks, parry2k. This is exactly perfect. Well worth the wait. Someday I'll examine the code more carefully and figure out how it works!