cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AlexPBIF Frequent Visitor
Frequent Visitor

Using FILTER or CALCULATE to create a measure that properly manipulates the filter context

The Excel file described herein is avaialbe at (data is in Sheet1, PivotTable is in Sheet2):

https://www.dropbox.com/s/4z4s5b2kgtq4knf/PBI%20Matrix%20example.xlsx?dl=0

 

The Power BI file described herein is available at:

https://www.dropbox.com/s/2p1jkc0wn9scasf/PBI%20Matrix%20example.pbix?dl=0

 

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.)

PT pic.PNG

 

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):

DM table pic.PNG

 

 

(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:

PT field settings pic.PNG

 

 

 

 

 

 

 

 

 

 

 

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:

Matrix pic.PNG

 

Please help.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Using FILTER or CALCULATE to create a measure that properly manipulates the filter context

@AlexPBIF Hope this is what you need, solution attached

 

image.png





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




6 REPLIES 6
Super User
Super User

Re: Using FILTER or CALCULATE to create a measure that properly manipulates the filter context

@AlexPBIF this will do it , correct?

 

image.png





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




AlexPBIF Frequent Visitor
Frequent Visitor

Re: Using FILTER or CALCULATE to create a measure that properly manipulates the filter context

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?

Super User
Super User

Re: Using FILTER or CALCULATE to create a measure that properly manipulates the filter context

@AlexPBIF sorry have no clue what you are talking about. If you can show with snapshot will help.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




AlexPBIF Frequent Visitor
Frequent Visitor

Re: Using FILTER or CALCULATE to create a measure that properly manipulates the filter context

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:

PT pic focused.PNG

 

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)%.

Super User
Super User

Re: Using FILTER or CALCULATE to create a measure that properly manipulates the filter context

@AlexPBIF Hope this is what you need, solution attached

 

image.png





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




AlexPBIF Frequent Visitor
Frequent Visitor

Re: Using FILTER or CALCULATE to create a measure that properly manipulates the filter context

Thanks, parry2k. This is exactly perfect. Well worth the wait. Someday I'll examine the code more carefully and figure out how it works!