Grand Total of Two Columns in a Matrix

[Anonymous]

Trying to write a measure to display the product of "Prod Lvl Exp Rate" * "Total Product Units" illustrated below. where "Total Product Units" is a a Measure: SUM('Billing' [Units])  and "Prod_Lvl_Exp_Rate" is a column in another table outside of billing, with the "Product Code" field relating the tables.

[Anonymous · Accepted solution]

I was able to figure out my own solution...  I had to create a new measure for my calculation then utilize HASONEVALUE.

Product Level Expense = IF(HASONEVALUE('Product Level Allocation Exp'[Prod Lvl Exp Rate]), [Prod Rate x Units row], SUMX(VALUES('Product Level Allocation Exp'[Prod Lvl Exp Rate]), [Prod Rate x Units row])).

[Anonymous]

Hi @Anonymous ,

According to your snapshot, it seems like you need to apply multiple aggregations on total level. You need to add if statement to check contents level and write specific formula to calculate on total level.

Measure Totals, The Final Word

If above not help, can you please share some sample data for test?

Regards,

Xiaoxin Sheng

[jdbuchanan71]

Hello @Anonymous, 

Give this a try

Mesure = [Total Product Units] * CALCULATE ( SUM ( Table[Prod_Lvl_Exp_Rate] ) )

[Anonymous]

Same result, the sum of the Measure column (grand total) does not equal the sum of the rows.

[jdbuchanan71]

Ahh, I didn't understand that was where you were having the issue.  You will want to use a SUMX to get the total to add up correctly.  Something along the lines of:

Measure = SUMX ( Table2[Product Code], Table2[Prod Lvl Exp Rate] * [Total Product Units] )

If this doesn't quite solve the issue, would you be able to share your .pbix?  It helps to see the layout of the model and understand the fields involved.