Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Zoey28
New Member

Using DAX VAR to calculate total

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

 

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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

 

Hi  @Zoey28 ,

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.

Greg_Deckler
Super User
Super User

@Zoey28 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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.