topic Using DAX VAR to calculate total in DAX Commands and Tips

Using DAX VAR to calculate total

Anonymous — Thu, 04 Apr 2024 19:36:55 GMT

Hello,

I am using DAX to calculate Year2 values based on Year1 inputs. There are 3 variables (VAR)

1. To calculate Year 2 Management Expenses (Y1 Market Rent * growth rate)

2. To calculate Year 2 OpEx (Y1 Market Rent from * growth rate)

3. To calculate Total Expenses (resulted from 1 +2)

I am showing the calculations as a column in table. THe values of management Expenses and OpEx is showing up correctly in the table; however, the total Expenses is BLANK.

Do we know any reason why this happens?

Here is my Script

Y3_test =

VAR PreviousMarketRentsValue =

CALCULATE(

MAXX(

FILTER(

'Properties',

'Properties'[cf_category] = "Market Rents"

[Y1]

ALL('Properties')

)

VAR Y2_ManagementExpenses = IF(

HASONEVALUE('Properties'[cf_category]) && VALUES('Properties'[cf_category]) = "Management Expenses",

((PreviousMarketRentsValue * 'Rental Growth Rate'[Parameter Value]) + PreviousMarketRentsValue) * 'Management Expenses Rate'[Management Expenses Rate Value],BLANK())

VAR Y2_OpEx = IF(

HASONEVALUE('Properties'[cf_category]) && VALUES('Properties'[cf_category]) = "Operations and Maintenance Expenses (OpEx)",

((PreviousMarketRentsValue * 'Rental Growth Rate'[Parameter Value]) + PreviousMarketRentsValue) * 'Operations and Maintenance Expenses (OpEx)'[Operations and Maintenance Expenses (OpEx) Value], BLANK())

VAR Y2_TotalExpenses = Y2_ManagementExpenses+Y2_OpEx

RETURN

IF(

HASONEVALUE('Properties'[cf_category]),

SWITCH(

TRUE(),

VALUES('Properties'[cf_category]) = "Management Expenses", Y2_ManagementExpenses,

VALUES('Properties'[cf_category]) = "Operations and Maintenance Expenses (OpEx)", Y2_OpEx,

VALUES('Properties'[cf_category]) = "Total Expenses", Y2_TotalExpenses,

Y2_TotalExpenses

BLANK()

)

Re: Using DAX VAR to calculate total

Greg_Deckler — Thu, 04 Apr 2024 19:52:55 GMT

Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Re: Using DAX VAR to calculate total

Anonymous — Tue, 23 Apr 2024 07:49:08 GMT

Thanks for the reply from Greg_Deckler , please allow me to provide another insight:

Hi Anonymous ,

Does a blank total mean that the Total row placed in the form visual does not show the correct value?

Measure follows the context of the "Total" row and is calculated in that context. Therefore, using a measure in a column of a table visualization may have unexpected values in the "Total" column.

You can use the following measure, using the IF()+HASONEVALUE() function, to determine if it is Total, and if it is, you can calculate it according to your own needs, such as Average, Sum, etc.

You can try using the following measure.

Measure_ totals = var _table=SUMMARIZE('Table','Table'[Place the first column of the matrix],"Value",[ Y3_test]) return IF( HASONEVALUE('Table'[Place the first column of the matrix]),[ Y3_test],SUMX(_table,[Value]))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.