Trouble getting grand total of DAX measure in matrix visual

[czaldumbide]

Hey Guys,

I am having trouble creating a measure that correctly calculates the total when added to a pivot table.

I am attaching a sample PBIX file for you to use. 

https://drive.google.com/drive/folders/1ofIgAsGLc2VU7qLoFkdaz2vAqbjhsdH-?usp=sharing 

The goal of this report is to calculate how much income each customer generates. Currently, I have two sources of income per customer, and I want to add a 3rd one by looking at a different table. To do this I am using 2 tables.

The first one, “Monthly Interest Accruals”, is a table that has a single monthly record per customer. The following table depicts the format and columns for your reference.

Using this table, I simply drag Date. [Month] to my columns, Customer Name to my rows, and Interest, and Fund Earnings as my 2 values.

This gives me the following matrix visualization.

To this matrix, I want to add a 3rd value of income called “Below Min Average Fee”. This refers to a $100 dollar charge that is made to an account when the monthly average balance is below the requirement. To calculate this, I will use the second table, “General Ledger History”, which contains the past year records for all account’s balances. Below are a few rows of the table to give you a reference of how the table looks like.

Using this table, I calculate the average of an account per month and see if it was below the requirement amount or not and flag that account. To do this I first create a calculated column with the minimum balance requirement. This measure basically says that the minimum average is $10,000 from January to July, and $50,000 from August to December.

Min Bal = IF(‘General Ledger History’[Date].[MonthNo] < 8, 10000, 50000)

Then, I create a measure to classify those accounts that will be charged the fee as 1 and those that do not as 0. The measure is as follows:

IsMinBal =

 IF(COUNTROWS(VALUES( 'General Ledger History'[Min Bal] ))=1,

        IF(VALUES( 'General Ledger History'[Min Bal] ) >  AVERAGE('General Ledger History'[Balance]),

         100,

         0 ),

   BLANK()

)

To test that the measure works, I created the following visualization:

As you can see those accounts with average below Min Bal for a month appear as 1 and the rest as 0.

Now my question to you, is how can I use this measure that flags the accounts, to show a charge on each respective account and month on the first matrix I created? Basically, I would like to get the following only that in the column of Below Min Balance Fee I would have 100 on each account that was flagged and the summation of all the charges as the total. (NOTE*= if there are more than one account per client that was flagged then the charge for that client on that month would be $100  x  # of accounts flagged).

Any tips on how to solve this would be of great help! Thank you

[v-diye-msft]

Hi @czaldumbide 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly.thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[czaldumbide]

Thank you for your solution. This definitely brings me a step closer to what I needed. When the minimum balance changes to 50,000 the penalty is not working properly so I might need to look further into it. 

[Ashish_Mathur]

Hi,

I am not sure whom you are replying to but did you try my solution?

[rajulshah]

Hello @czaldumbide,

Maybe this link can help you achieve what you want.

Please let me know if it didn't.